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 at 17:16
  • dbfiddle.uk/… – BrDaHa Apr 22 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

| improve this answer | |
  • I humbly disagree with you: dbfiddle.uk/… – tavlima Apr 17 at 20:29
  • Well the join makes no sense whatsoever – a_horse_with_no_name Apr 17 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 at 20:52
  • @tavlima: why not simply leave out the type completely? (jb_column #>> '{schrodinger,foo}')::numeric as s_foo? – a_horse_with_no_name Apr 18 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 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.