CODEWITHSUNDEEP
postgresql
Sanya
Sanya

Reputation: 21

How to merge multiple json objects into one by key? (postgresql)

I am getting an array from json objects, but I want to get one object that will have many keys and values.

select 
    t1.column1, 
    jsonb_agg(t2.column2) 
from table t1
left join table t2
group by column1

I have - [{"0001": 75.00, "0002": 60.00}, {"0001": 50.00}]

I want - {"0001": 50.00, "0002": 60.00}

I found an example like this, but I can't figure out how to apply it to my example.

with name as (
  select '{"1": 5, "2": 53}'::jsonb as val  union
  select '{"1": 74, "2": 34, "3": 54}'::jsonb as val
)
select jsonb_object_agg(jv.key, jv.value order by jv.value asc) as merged_object from name
left join jsonb_each(column) jv on true;

Help me please

Upvotes: 1

Views: 986

Answers (2)

Sanya
Sanya

Reputation: 21

I decided the question like this

jsonb_object_agg(coalesce(cl.key, '0'), cl.value order by cl.value desc)
...
left join jsonb_each(column) cl on true

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 248305

You have to take apart the JSONs and re-assemble them:

select 
    t1.column1, 
    jsonb_object_agg(t3.key, t3.value) 
FROM t1
   LEFT JOIN t2 ON something
   CROSS JOIN LATERAL jsonb_each(t2.column2) AS t3
GROUP BY t1.column1;

Upvotes: 2

Related Questions