CODEWITHSUNDEEP
postgresql
ash
ash

Reputation: 73

performing a group by in JSON aggregate

I have table format like this below

parentid date time value
1 '2021-03-15' '18:30' 35
2 '2021-03-17' '15:30' 15
2 '2021-03-16' '13:30' 25
1 '2021-03-15' '10:30' 45

I want the data to be grouped by parentid first and then for each parentit have json. For an example

   ParentID         JSON

      1            [
                   {"2021-03-15": [{"10:30:00": 45}, {"18:30:00": 35}]}
                   ]

      2            [
                   {"2021-03-16": [{"13:30:00": 25}]}, 
                   {"2021-03-17": [{"15:30:00": 35}]} 
                   ]

i.e group by dates and then time and its corresponding value. I'm using json_aggr and json_build_object but I cant find a way to group the dates.

Upvotes: 1

Views: 7862

Answers (2)

S-Man
S-Man

Reputation: 23676

step-by-step demo:db<>fiddle

It is not clear to me, why you want to create {"2021-03-15": [{"10:30:00": 45}, {"18:30:00": 35}]} instead of {"2021-03-15": {"10:30:00": 45, "18:30:00": 35}}. The array seems not necessary for me.

Same for [{"2021-03-16": [{"13:30:00": 25}]}, {"2021-03-17": [{"15:30:00": 35}]}] instead of {"2021-03-16": {...}, "2021-03-17": {...}}.

However you requested result can be achieved by this query:

SELECT
    parentid,
    json_agg(new_object)                          -- 4
FROM (
    SELECT
        parentid,
        "date",
        json_build_object("date",                 -- 3
            json_agg(                             -- 2
                json_build_object("time", value)  -- 1
            )
        ) as new_object
    FROM mytable
    GROUP BY parentid, "date"
) s
GROUP BY parentid
  1. Create your time object
  2. Aggregate the time objects into an array (using GROUP BY)
  3. Create the date object with the new array
  4. Aggregate all date objects into an array (using a second GROUP BY)

Upvotes: 3

user330315
user330315

Reputation:

You need to two-stop aggregation. First aggregate the times by parentid and date. Then aggregate those JSON values per parentid:

select parentid, 
       jsonb_object_agg("date", times)
from (       
  select parentid, 
         "date"::text as "date", 
         jsonb_agg(jsonb_build_object("time"::text, value)) as times
  from the_table 
  group by parentid, "date"
) t
group by parentid  
;

Upvotes: 1

Related Questions