How to group row into an array [PostgreSQL]

Question

TableA:

date_time                     amount    note
2016-03-01 01:00.00.000000    100       "hi"
2016-03-01 02:00.00.000000    5         "hello"
2016-03-01 03:00.00.000000    2         "foo"
2016-04-01 00:00.00.000000    60        "bar"

I need to output the final table like this:

ProcessedTable

grouped_date_time     row          total
2016-03-01            RowA         107
2016-04-01            RowB         60

RowA is an array of JSON objects from all rows in the first group:

[
  { date_time:2016-03-01 01:00.00.000000, amount:100, note:"hi"}, 
  { date_time:2016-03-01 02:00.00.000000, amount:5, note:"hello"}, 
  { date_time:2016-03-01 03:00.00.000000, amount:2, note:"foo"}
]

RowB is an array of JSON objects from all rows in the first group:

[
  { date_time:2016-04-01 00:00.00.000000, amount:60, note:"bar"} 
]

Currently I have query like this

SELECT date_trunc('day', date_time), array_agg(amount)
    FROM table_a
GROUP BY date_trunc('day', date_time)

I am not sure what to put for array_agg(amount) to extract out the whole JSON object as a column, and the total amount for each group.

clemens · Accepted Answer · 2018-01-24 06:29:56Z

2

The following should work:

SELECT DATE_TRUNC('day', date_time), 
    JSON_AGG(ROW_TO_JSON(table_a)) AS row, 
    SUM(amount) AS total
FROM table_a GROUP BY DATE_TRUNC('day', date_time);

While ROW_TO_JSON(table_a) converts a SQL row to a JSON object, JSON_AGG collects all objects into a JSON array.

Output (with use of JSONB_PRETTY):

     date_trunc      |                    row                     | total 
---------------------+--------------------------------------------+-------
 2016-03-01 00:00:00 | [                                         +|   107
                     |     {                                     +| 
                     |         "note": "hi",                     +| 
                     |         "amount": 100,                    +| 
                     |         "date_time": "2016-03-01T01:00:00"+| 
                     |     },                                    +| 
                     |     {                                     +| 
                     |         "note": "hello",                  +| 
                     |         "amount": 5,                      +| 
                     |         "date_time": "2016-03-01T02:00:00"+| 
                     |     },                                    +| 
                     |     {                                     +| 
                     |         "note": "foo",                    +| 
                     |         "amount": 2,                      +| 
                     |         "date_time": "2016-03-01T03:00:00"+| 
                     |     }                                     +| 
                     | ]                                          | 
 2016-04-01 00:00:00 | [                                         +|    60
                     |     {                                     +| 
                     |         "note": "bar",                    +| 
                     |         "amount": 60,                     +| 
                     |         "date_time": "2016-04-01T00:00:00"+| 
                     |     }                                     +| 
                     | ]                                          |

Edit: The query can be even further simplified by removing ROW_TO_JSON:

SELECT DATE_TRUNC('day', date_time), JSON_AGG(t) AS row, SUM(amount) AS total
FROM table_a t GROUP BY DATE_TRUNC('day', date_time);

edited Jan 24, 2018 at 6:29

answered Jan 23, 2018 at 5:51

clemens

17.9k12 gold badges52 silver badges71 bronze badges

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

3 Comments

jimmu Over a year ago

whoa. you're passing table_a to a function. I had to do complicated gymnastics to get an individual row. This is neat!

clemens Over a year ago

@Krishna: No, I don't put the table to ROW_TO_JSON. ta is a reference for the current row. You can use a table alias or a comma separated list of column names as well.

jimmu Over a year ago

the more you know! Kinda makes sense that there would be such functionality built in. My first attempt was to pass * to row_to_json which did not work.

jimmu · Accepted Answer · 2018-01-23 06:00:48Z

0

You can select whole row before passing into row_to_json, use cte and group by

with t1 as(
select date_time as dt, amount,
   (select row_to_json(this_row) 
      from (select t.date_time, t.amount, t.note) as this_row) as rowdict
from table_a as t
)
select date_trunc('day', dt), array_agg(rowdict) as row, sum(amount) as total
from t1
group by 1;

edited Jan 23, 2018 at 6:00

answered Jan 23, 2018 at 5:53

jimmu

1,0561 gold badge10 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to group row into an array [PostgreSQL]

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related