How do I group by identifier and create an array of JSON objects in postgreSQL?

Question

I have a table of transactions where I need to group by customerId and create aggregated JSON records in an array.

customerId|transactionVal|day
      1234|             2|2019-01-01
      1234|             3|2019-01-04
        14|             1|2019-01-01

What I'm looking to do is return something like this:

customerId|transactions
      1234|[{'transactionVal': 2, 'day': '2019-01-01'}, {'transactionVal': 3, 'day': '2019-01-04'}]
        14|[{'transactionVal': 1, 'day': '2019-01-01'}]

I will need to later iterate through each transaction in the array to calculate % changes in the transactionVal.

I searched for a while but could not seem to find something to handle this as the table is quite large > 70mil rows.

Thanks!

jmunsch · Accepted Answer · 2019-06-28 21:26:48Z

2

Should be possible to use array_agg and json_build_object like so:

ok=# select customer_id, 
     array_agg(json_build_object('thing', thing, 'time', time)) 
     from test group by customer_id;

 customer_id |                                             array_agg                                             
-------------+---------------------------------------------------------------------------------------------------
           2 | {"{\"thing\" : 1, \"time\" : 1}"}
           1 | {"{\"thing\" : 1, \"time\" : 1}",
                "{\"thing\" : 2, \"time\" : 2}",
                "{\"thing\" : 3, \"time\" : 3}"}
(2 rows)

ok=# select * from test;
 customer_id | thing | time 
-------------+-------+------
           1 |     1 |    1
           2 |     1 |    1
           1 |     2 |    2
           1 |     3 |    3
(4 rows)

ok=#

answered Jun 28, 2019 at 21:26

jmunsch

24.3k12 gold badges102 silver badges120 bronze badges

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

Collectives™ on Stack Overflow

How do I group by identifier and create an array of JSON objects in postgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related