By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
with data (jsonbcolumn) as (
values
('{"room": ["101"],"equipments": ["thermometer"]}'::jsonb),
('{"room": ["101","102"], "equipments": ["stethescope"]}')
)
select jsonb_build_object(
'room', jsonb_agg(distinct r.room),
'equipments', jsonb_agg(distinct e.eq)
)
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(eq)
| jsonb_build_object |
|---|
| {"room": ["101", "102"], "equipments": ["stethescope", "thermometer"]} |