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.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

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

2 Answers 2


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

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

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:


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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.