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?

Answer 1

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