How can I coalesce
a null
column into an empty JSONB
array? This doesn't work:
SELECT jsonb_array_elements(coalesce(null_column, '{}'::jsonb))
FROM table
WHERE id = 13;
-- ERROR: cannot extract elements from an object
Neither this:
SELECT jsonb_array_elements(coalesce(null_column, '[]'::jsonb))
FROM table
WHERE id = 13;
-- ERROR: cannot extract elements from a scalar
{}
is an object but jsonb_array_elements
expects an array, so replace {}
with []
Make sure that both arguments return a jsonb array. For example, if your column is an integer, you can use concat
to add the square brackets and ::jsonb
for the conversion
SELECT jsonb_array_elements(coalesce(concat('[',my_column,']')::jsonb,'[]'::jsonb))