PostgreSQL Creating JSON Column From Existing Data

Question

Given data like the below:

+---+------------+------------
|id | change     | date       
+---+------------+------------
| 1 | name       | 2018-06-20
| 2 | address    | 2018-06-20
| 3 | email      | 2018-06-20
| 4 | email      | 2018-06-21
| 5 | address    | 2018-06-22
| 6 | address    | 2018-06-23

I'm trying to create a view that summarises the above into a single json column with data like:

{"name":["2018-06-20"], "address":["2018-06-20","2018-06-22","2018-06-23"], "email":["2018-06-20","2018-06-21"]}

I have been trying to figure it out using the array_aggr, array_to_json, json_agg, array_build_object functions but I can't seem to get it quite right.

I hope someone can help.

Cheers

klin · Accepted Answer · 2018-06-28 00:06:29Z

1

You should use jsonb aggregates twice for two levels:

select jsonb_pretty(jsonb_object_agg(change, dates))
from (
    select change, jsonb_agg(date) as dates
    from my_table
    group by change
    ) s

     jsonb_pretty      
-----------------------
 {                    +
     "name": [        +
         "2018-06-20" +
     ],               +
     "email": [       +
         "2018-06-20",+
         "2018-06-21" +
     ],               +
     "address": [     +
         "2018-06-20",+
         "2018-06-22",+
         "2018-06-23" +
     ]                +
 }
(1 row)

Note that jsonb_pretty() is unnecessary, used only for a nice output.

answered Jun 28, 2018 at 0:06

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

WhiteFire Over a year ago

Thanks! I thought I read somewhere that you couldn't nest agg functions. I must have misunderstood.

klin Over a year ago

You cannot directly nest aggregates, this is impossible: jsonb_object_agg(change, jsonb_agg(data)) -- ERROR. You have to use a derived table (or CTE) like in the answer.

Collectives™ on Stack Overflow

PostgreSQL Creating JSON Column From Existing Data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related