CODEWITHSUNDEEP
postgresql
Alex
Alex

Reputation: 845

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

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?

Upvotes: 2

Views: 1568

Answers (2)

user330315
user330315

Reputation:

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 if 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

Upvotes: 5

jian
jian

Reputation: 4824

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;

Upvotes: 1

Related Questions