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
First, a table:
CREATE TABLE my_table ( id INTEGER NOT NULL, jb_column JSONB );
The structure I'm trying to extract from the
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:
 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.
edit: online example https://dbfiddle.uk/?rdbms=postgres_11&fiddle=66c90482a33aa458c6a90e1bbaa1ec9d