Search code examples
postgresqljsonb

Conditionally do jsonb_array_elements

I have a jsonb column called info that some type have the form of an object: { id: 2 }

and some other types it has the shape of an array: [{ id: 2 }]

I have a query that does this:

SELECT * FROM users
CROSS JOIN LATERAL jsonb_array_elements(users.info)

Now, if my data set has just arrays in the info column, there is no issue, but if there is one that is not an array, but rather an object, I get the error:

ERROR: cannot extract elements from an object

Is there a way in PostgreSQL for that CROSS JOIN LATERAL to ignore the rows that are not arrays in the info column?

Solution

  • Use the function jsonb_typeof() to eliminate rows with non-array jsonb column:

    select *
from (
    select *
    from users
    where jsonb_typeof(info) = 'array'
    ) s
cross join lateral jsonb_array_elements(info);

    Working example in rextester.