How to group row into an array [PostgreSQL]

1
votes

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.

2
postgresql

2 Answers

2
votes

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);
0
votes

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;