Postgres coalesce to empty JSONB array

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

It really depends on the exact definition of `null_column`, the data allowed in it and your version of Postgres. — Erwin Brandstetter, Dec 17 '16 at 07:16

FuzzyTree · Accepted Answer · 2016-12-17T02:42:48.373

9

{} 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))

edited Dec 17 '16 at 02:42

answered Dec 17 '16 at 02:34

FuzzyTree

32,014
3
54
85

1

This works nice but for some weird reason, I still get `cannot extract elements from a scalar` if I use my column name instead of `null`. – Sam R. Dec 17 '16 at 02:40
1

@norbertpy forgot to address that - you will have to convert the column value to an json array because both arguments in the coalesce must return a json array – FuzzyTree Dec 17 '16 at 02:41
1

My column is JSONB for sure, but seems like Postgres cannot tell it's null. Even this `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`. – Sam R. Dec 17 '16 at 03:13
But that will be another question. Thanks for the inputs. – Sam R. Dec 17 '16 at 03:14
3

@norbertpy are you sure col should be null as opposed to the string that represents null in json? – FuzzyTree Dec 17 '16 at 03:16

score 2 · Answer 2 · edited Apr 26 '18 at 15:37

2

Here is SQL code for accomplishing what you want:

SELECT jsonb_array_elements(coalesce(null_column, '[{}]'::jsonb))
FROM table
WHERE id = 13;

edited Apr 26 '18 at 15:37

Jules Dupont

7,259
7
39
39

answered Apr 26 '18 at 14:41

Louis Rebolloso

15
5

for just an empty array I think it's more like, `'[]'::jsonb` but thanks, you got me there with your answer – ABCD.ca Jul 18 '22 at 17:57

Postgres coalesce to empty JSONB array

2 Answers2