Reputation: 21
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
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
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