Question: Postgres coalesce to empty JSONB array

Question

Postgres coalesce to empty JSONB array

Answers 1
Added at 2016-12-17 02:12
Tags
Question

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
Answers to

Postgres coalesce to empty JSONB array

nr: #1 dodano: 2016-12-17 03:12

{} 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))
Source Show
◀ Wstecz