Extract and combine multiple values from a jsonb column

Question

I am looking to extract multiple values from a jsonb column in Postgres, and am running into an issue where some values are coming back null.

fiddle

Setting up schema:

create table jsonb_test (test jsonb);
insert into jsonb_test values('{
"title": "test",
"tags": [
    {"tag": 1},
    {"tag": 2}
]
}'::jsonb);

Query that I am running:

select jsonb_path_query(test, '$.title'::jsonpath) as title,
       jsonb_path_query(test, '$.tags.tag'::jsonpath) as tag
from jsonb_test

The result that I am looking for should be:

| title | tag |
|-------|-----|
| test  | 1   |
| test  | 2   |

and what I am getting is:

| title | tag |
|-------|-----|
| test  | 1   |
| null  | 2   |

For the query, I have tried everything I can think of to make the null go away, selecting the second set of values from a cross join as opposed to the same table, aggregating it into a regular array and then using unnest, etc, but I can't seem to get it to work, and more importantly, I don't understand what is causing that second null.

What is the easiest way (performance is not expected to be an issue here) to get the result that I am looking for?

Please put all essential code into the question, not just the fiddle - especially not on a fiddle site that requires to run scripts from multiple sites. — Erwin Brandstetter, Mar 19 '20 at 01:41
@ErwinBrandstetter - what do you mean by especially not on a fiddle site that requires to run scripts from multiple sites.? I see db<>fiddle as a Godsend - different servers (even Firebird, for which I have a fondness - SQL in check constraints!) and other features (e.g. compare) - so what's the problem with scripts from multiple sites? I mean, don't most sites run scripts from multiple sites? I do agree with you though that code should be included in the answer as well as in a fiddle! — Vérace, Mar 19 '20 at 02:05
@Vérace: I am a fan and supporter of Jack's db<>fiddle site from the first day - which works even without activating scripts from third-party sites. The link here is to www.db-fiddle.com - a different site altogether, which does not work without scripts from multiple third-party sites. Aside from that, it's the standing rule here that essential parts of the question must be included on-site, not just linked to. Links can die quickly. — Erwin Brandstetter, Mar 19 '20 at 02:09
Oh, and I do not like running scripts from third-party sites I don't trust. My NoScript prevents that from happening. — Erwin Brandstetter, Mar 19 '20 at 02:18

Erwin Brandstetter · Accepted Answer · 2020-03-19T02:23:07.997

jsonb_path_query() is a set-returning function. When putting more than one of those in a SELECT list, this is the expected behavior. See:

What is the expected behavior for multiple set-returning functions in SELECT clause?

You seem to be looking for a CROSS JOIN instead:

SELECT *
FROM       (SELECT jsonb_path_query(test, '$.title'::jsonpath) AS title FROM jsonb_test) a
CROSS JOIN (SELECT jsonb_path_query(test, '$.tags.tag'::jsonpath) AS tag FROM jsonb_test) b

Alternatively, and probably more elegantly, use two LATERAL joins:

SELECT a.title, b.tag
FROM   jsonb_test j
     , jsonb_path_query(j.test, '$.title'::jsonpath) a(title)
     , jsonb_path_query(j.test, '$.tags.tag'::jsonpath) b(tag);

db<>fiddle here

What is the difference between LATERAL and a subquery in PostgreSQL?

Effectively, a "proxy cross join". See:

Understanding multiple table join with aggregation

Extract and combine multiple values from a jsonb column

1 Answers1