PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: return json as a list

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'}]
Tags:

2

Answers


  1. 0

    Use jsonb_typeof(). It’s not elegant, but it works:

    create table my_table (column_a jsonb, column_b jsonb);
insert into my_table values 
  ('{"field0": "value0"}', '[{"field1": "value1"}, {"field2": "value2"}]'),
  (null, '[{"field1": "value1"}, {"field2": "value2"}]'),
  (null, null)
;

select case jsonb_typeof(coalesce(column_a, column_b))
         when 'array' then coalesce(column_a, column_b)
         else jsonb_build_array(coalesce(column_a, column_b))
       end as result
  from my_table;

    Working fiddle

    Login or Signup to reply.
  2. 0

    Coalesce() doesn’t necessarily fit here because array-wrapping a null produces a non-null, single-element array. If you resort to a case, it can do coalesce()‘s job without its help:
    demo at db<>fiddle

    select case column_a is not null
       when true then json_build_array(column_a::json)
       else column_b
       end as res
from my_table;

    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:

    jsonb does not preserve white space, does not preserve the order of object keys, and does not keep duplicate object keys. If duplicate keys are specified in the input, only the last value is kept.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search