How to merge a JSONB array of objects into a single object in PostgreSQL

byMR

June 28, 2022

I have a JSONB column where each row contains an array of multiple objects.

'[{"a": 1}, {"b": 2}, {"c": 0.5}]'::jsonb

I want to merge all of them together into a single object:

'{"a": 1, "b": 2, "c": 0.5}'::jsonb

I have tried using coalesce to merge them without success. I’ve also been playing around with multiple other inbuilt functions from Postgres but can’t seem to figure this one out.

Any ideas?

>Solution :

You need to unnest the array and then aggregate the key/values back into a single object:

select (select jsonb_object_agg(e.ky, e.val) 
        from jsonb_array_elements(t.the_column) as x(element)
          cross join jsonb_each(x.element) as e(ky,val))
from the_table t;

Note that the array contains duplicate keys, the "last one" will win in this case (because JSON doesn’t allow duplicate keys)