Group json object by keys postgresql

Question

So I have a table called data which have 4 columns name (string), value (string), created_at (timestamp), r_id (int8) and I'm trying to get a JSON object which give me all the values grouped by a concat of the name and r_id. The closest I've achieved is as following:

SELECT json_build_object(concat(name, r_id), json_build_array("data".value,created_at))
FROM data

Gives:

{"Name1" : ["69.39", "2018-02-19T16:27:17.482111"]}
{"Name1" : ["69.66", "2018-02-19T16:26:21.075247"]}
{"Name1" : ["69.93", "2018-02-19T16:26:28.809292"]}
{"NewName1" : ["25.75", "2018-02-19T16:27:29.024595"]}
...

Expected:

{"Name1" : [["69.39", "2018-02-19T16:27:17.482111"],["69.66", "2018-02-19T16:26:21.075247"],["69.93", "2018-02-19T16:26:28.809292"]]}
{"NewName1" : [["25.75", "2018-02-19T16:27:29.024595"]]}
...

try SELECT json_build_object(concat(name, r_id), json_agg(json_build_array("data".value,created_at))) FROM data group by concat(name, r_id) — Vao Tsun
– Vao Tsun, Commented Mar 9, 2018 at 8:37
It worked Great! I just also need to order it by created_at. If I try to add ORDER BY created_at ASC It messes up again :\ — acrogenesis
– acrogenesis, Commented Mar 9, 2018 at 9:08
this worked SELECT json_build_object(concat(name, r_id), json_agg(json_build_array(value,created_at) ORDER BY created_at ASC)) FROM data group by concat(name, r_id) — acrogenesis
– acrogenesis, Commented Mar 9, 2018 at 9:14

Vao Tsun · Accepted Answer · 2018-03-09 09:17:01Z

2

you can use json_agg to aggregate arrays over:

SELECT json_build_object(concat(name, r_id), json_agg(json_build_array("data".value,created_at))) 
FROM data group by concat(name, r_id)

of course if you need it ordered you can add order:

SELECT json_build_object(concat(name, r_id), json_agg(json_build_array(value,created_at) ORDER BY created_at ASC))
FROM data
group by concat(name, r_id)

answered Mar 9, 2018 at 9:17

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

Group json object by keys postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related