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.


Setting up schema:

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

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 at 1:41
  • @ErwinBrandstetter Done, – soandos Mar 19 at 1:45
  • @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 at 2: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 at 2: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 at 2:18

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:

You seem to be looking for a CROSS JOIN instead:

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

Effectively, a "proxy cross join". See:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.