How to use Postgres with JSON to create objects with 3 levels-CodePudding

Suppose I had rows like

id	group1	group2	value
5	1	2	55
5	1	3	66
5	2	1	66
4	1	3	77

I'm looking to group on id so I'd have two rows with

| id       | json         
| -------- | -------------
| 5        | {1: {2: 55, 3: 66}, 2: {1: 66}}        
| 4        | {1: {3: 77}}

I've tried combinations of JSON_OBJECT_AGG wrapped in JSON_BUILD_OBJECT with the results going to JSON_AGG. That is almost giving me what I want. The items are in an array. Each object in the array has only one outer key and all the inner keys are as expected. If I could combine all the objects in the array or just build the objects correctly, either way would solve the issue.

CodePudding user response：

You need to use jsonb_object_agg() twice, first aggregating by id, group1 and then grouping only by id:

select id, jsonb_object_agg(group1, json) as json
from (
    select 
        id,
        group1,
        jsonb_object_agg(group2, value) as json
    from my_table
    group by id, group1
    ) s
group by id

Test it in db<>fidlle.