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

Question

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

Hi Sanya, why does is the "0001":50.00 in your result? What's the aggregation method in case of the same key? — Ftisiot
– Ftisiot, Commented Jul 22, 2021 at 14:14

Laurenz Albe · Accepted Answer · 2021-07-22 14:24:34Z

2

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;

answered Jul 22, 2021 at 14:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sanya · Accepted Answer · 2021-07-22 15:41:33Z

1

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

answered Jul 22, 2021 at 15:41

Sanya

214 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related