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))
cannot extract elements from a scalar if I use my column name instead of null.
SELECT (CASE WHEN col IS NULL THEN 'is-null' ELSE 'is-not-null' END) AS r FROM t WHERE id = 13; returns is-not-null.Here is SQL code for accomplishing what you want:
SELECT jsonb_array_elements(coalesce(null_column, '[{}]'::jsonb))
FROM table
WHERE id = 13;
null_column, the data allowed in it and your version of Postgres.