Extract and combine multiple values from a jsonb column 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?
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&lt;>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