CODEWITHSUNDEEP
sqljsonpostgresqlaggregate-functionsjsonb
Christopher Carlisle
Christopher Carlisle

Reputation: 63

Aggregation of Json objects on a relational table on Postgres

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.

Upvotes: 1

Views: 289

Answers (1)

GMB
GMB

Reputation: 222432

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}} |

Upvotes: 4

Related Questions