Postgresql – LEFT JOIN LATERAL conditions and JSONB

join;jsonpostgresql

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.jp_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.jp_column -> 'schrodinger') = 'object', to no avail.

Thoughts?

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

Best Answer

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