LEFT JOIN LATERAL conditions and JSONB

Question

I have a JSONB column (jb_column) that may have a key (let's call it schrodinger) or not. When the schrodinger key is present, it's value can either be null::JSONB or a regular JSONB object.

First, a table:

CREATE TABLE my_table
(
    id INTEGER NOT NULL,
    jb_column JSONB
);

The structure I'm trying to extract from the schrodinger column:

CREATE TYPE schrodinger AS
(
    foo NUMERIC,
    bar TEXT
);

And this was my initial approach:

SELECT mt.id, s.*
FROM my_table mt
LEFT JOIN LATERAL jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger') s

But this resulted in the annoying error below:

[22023] ERROR: cannot call populate_composite on a scalar

After a couple of hours trying to debug it, I nailed it down to the null::JSONP values of the schrodinger key. So, my next attempt was to add some criteria to the JOIN, so that PG could ignore those...

SELECT mt.id, s.*
FROM my_table mt
LEFT JOIN LATERAL jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger') s
          ON jsonb_typeof(mt.jb_column -> 'schrodinger') != 'null'

I'd expect this condition to skip the JOIN when both the key is not present as well as when it's there, but null::JSONP... Unfortunately that doesn't seem to be working, since I still get the same error.

I also tried jsonb_typeof(mt.jb_column -> 'schrodinger') = 'object', to no avail.

Thoughts?

edit: online example https://dbfiddle.uk/?rdbms=postgres_11&fiddle=66c90482a33aa458c6a90e1bbaa1ec9d

Answer 1

jsonb_populate_record can't be used like a table as it only returns a scalar values.

I think you want

select id, (jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger')).* 
from my_table mt

If the json value doesn't contain the key schrodinger then this will automatically return null

Online example

Answer 2

As you stated, the problem is when jsonb_typeof(mt.jb_column -> 'schrodinger') = 'null', there is nothing for jsonb_populate_record() to work on.

The real problem is with the (LEFT) OUTER JOIN. Roughly speaking, a OUTER JOIN keeps the rows where the join condition is NULL, which is the case for the jsonb operator -> according to the documentation:

Note

The field/element/path extraction operators return NULL, rather than failing, if the JSON input does not have the right structure to match the request; for example if no such key or array element exists.

For example, SELECT '{}'::jsonb-> 'a' IS NULL; is true under PostgreSQL 15.

Then, I think the NULL value triggers the error for forcing jsonb_populate_record to work on null.

To avoid the issue, you can use an INNER JOIN instead of the OUTER JOIN (dbfiddle):

SELECT mt.id, s.*
FROM my_table mt
INNER JOIN jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger') s
  ON jsonb_typeof(mt.jb_column -> 'schrodinger') != 'null';

Alternatively, using WHERE (dbfiddle) seems to solve the problem too:

SELECT mt.id, s.*
FROM my_table mt
LEFT JOIN jsonb_populate_record(null::schrodinger, mt.jb_column -> 'schrodinger') s
  ON true
WHERE jsonb_typeof(mt.jb_column -> 'schrodinger') != 'null';