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

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?

CodePudding user response：

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)

With Postgres 12 or later this can be simplified a little bit:

select (select jsonb_object_agg(e.item ->> 'key', e.item -> 'value')
        from jsonb_path_query(t.the_column, '$[*].keyvalue()') as e(item))
from the_table t

CodePudding user response：

CTE solution:

WITH cte (
    each_json
) AS (
    SELECT
        jsonb_path_query('[{"a": 1}, {"b": 2}, {"c": 0.5}]'::jsonb, '$[*]')
),
cte1 AS (
    SELECT
        (jsonb_each(each_json)).*
    FROM
        cte
)
SELECT
    jsonb_object_agg(key, value)
FROM
    cte1;