I have mixed types of values in the columns but all of them are json object:
one field stored as text in column_a:
{'field0': 'value0'}
another field as json (list of) in column_b:
[{'field1': 'value1'},{'field2','value2'}]
depending on emptiness of the fields, i am choosing one or another:
select
coalesce(column_a::json, column_b) as res
from my_table
In case of column_a presence, it returns as json object and it is ok.
How to get the result as list of jsons? I would like to have:
[{'field0': 'value0'}]
2
Answers
Use
jsonb_typeof()
. It’s not elegant, but it works:Working fiddle
Coalesce()
doesn’t necessarily fit here because array-wrapping anull
produces a non-null
, single-element array. If you resort to acase
, it can docoalesce()
‘s job without its help:demo at db<>fiddle
To make the columns more lightweight, faster to read/write/process, support more functions and operators including JSONPath expressions, index and search more efficiently, switch to
jsonb
.The price is small: