Could not merge multiple Jsonb rows into a single row

Question

Need to merge all the rows of my single jsonb column.

For example: my jsonb column has rows as the below one.

My input data is (2 rows):

{"room": ["101"],"equipments": ["thermometer"]}
{"room": ["101","102"], "equipments": ["stethescope"]}

upon running this query,

select (jsonb_each(jsonbcolumn)).* 
from table group by key, value

I get the following output,

    key         |       value
    equipments  |     ["stethescope"] 
    equipments  |     ["thermometer"]
    room        |     ["101","102"] 
    room        |     ["101"]

If I try to group by key add the values using jsonb_object_agg, jsonb eliminates the first value and retains only the second value.

{"room": ["101","102"],"equipments": ["stethescope"]}

If I try using json_object_agg, I get repetitive values

{ "room" : ["101"], "equipments" : ["thermometer"], "room" : ["101", "102"], "equipments" : ["stethescope"] }

My expected output is

{"room": ["101","102"], "equipments":["stethescope", "thermometer"]}

in a single row.

Tried out almost all solutions in the net. here are few links which i tried.

score 2 · Answer 1 · answered Apr 25 '20 at 09:01

The only solution I can think of is this:

select jsonb_build_object(
          'room', jsonb_agg(distinct r.room), 
          'equipments', jsonb_agg(distinct e.equipment) 
       )
from data d
  cross join jsonb_array_elements_text(d.jsonbcolumn -> 'room') as r(room)
  cross join jsonb_array_elements_text(d.jsonbcolumn -> 'equipments') as e(equipment)

but that's going to be extremely inefficient (but that's the price you pay for de-normalizing)

Online example

Could not merge multiple Jsonb rows into a single row

1 Answers1