Aggregation of Json objects on a relational table on Postgres

Question

I have a relational table that contains three variable values, an ID, and the date broken down into parts.

ID | Year | Month | Day | A | B | C | 
 1   2019     12    01    5   10  15
 1   2019     12    02    2   4   6 
 1   2019     11    01    1   2   3
 1   2019     11    02    10  9   8

The goal is to aggregate that data into a JSONB/Relational Table, with the columns A, B, and C in a JSONB object: {A:5, B:10, C:15} which is the value to another JSONB object with the Day as the key.

{01:{A:5, B:10, C:15}}

Additionally, I want to aggregate the data so that all of the data for every month is within one object, with the final result looking like the following:

ID | Year | Month | Data                                     |
 1   2019    12     {01:{A:5, B:10, C:15},02:{A:2, B:4, C:6}}
 1   2019    11     {01:{A:1, B:2, C:3},02:{A:10, B:9, C:8}}

I've been able to get as far as creating an the data with a row for every day, but I'm getting stuck on aggregating them all by month, year, and ID.

Any help would be greatly appriciated.

Please also include the query that you managed to write in your question, even if it doesn't work. — Bergi
– Bergi, Commented Oct 22, 2019 at 23:57

GMB · Accepted Answer · 2019-10-22 23:54:25Z

4

You can use json_build_object() and json_object_agg():

select
    id,
    year,
    month,
    json_object_agg(day, json_build_object('A', A, 'B', B, 'C', C)) "data"
from mytable
group by
    id,
    year,
    month

Demo on DB Fiddle:

| id  | year | month | data                                                |
| --- | ---- | ----- | --------------------------------------------------- |
| 1   | 2019 | 11    | {"1":{"A":1,"B":2,"C":3},"2":{"A":10,"B":9,"C":8}}  |
| 1   | 2019 | 12    | {"1":{"A":5,"B":10,"C":15},"2":{"A":2,"B":4,"C":6}} |

answered Oct 22, 2019 at 23:54

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

Christopher Carlisle Over a year ago

Worked perfectly. Thank you very much, I was over thinking it.

GMB Over a year ago

Welcome @ChristopherCarlisle! Glad that it helped.

Collectives™ on Stack Overflow

Aggregation of Json objects on a relational table on Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related