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:

    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.


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

  • Could you use a CTE to omit the records out that have a null value for schrodinger? otherwise the other thing you may try could be a case statement on your join (not sure if that would work, but might get you going in the right direction) clarkdave.net/2015/03/…
    – BrDaHa
    Apr 22 '20 at 17:16
  • dbfiddle.uk/…
    – BrDaHa
    Apr 22 '20 at 17:24

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

  • I humbly disagree with you: dbfiddle.uk/…
    – tavlima
    Apr 17 '20 at 20:29
  • Well the join makes no sense whatsoever Apr 17 '20 at 20:37
  • Going that way, would it be possible to, let's say, alias schrodinger.foo and schrodinger.bar as s_foo and s_bar?
    – tavlima
    Apr 17 '20 at 20:52
  • @tavlima: why not simply leave out the type completely? (jb_column #>> '{schrodinger,foo}')::numeric as s_foo? Apr 18 '20 at 6:10
  • Because the post was just a trivial example to explain the problem. The actual type has around 15 fields and that level of verbosity would soon render the query unmaintanable (I’m actually building a view on top of that jsonb data), while possibly reducing the performance (if I got PG docs correctly).
    – tavlima
    Apr 18 '20 at 20:34

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.