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

Question

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.

klin · Accepted Answer · 2022-09-19 22:05:55Z

1

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.

answered Sep 19, 2022 at 22:05

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

kagronick Over a year ago

You are my hero. Thank you!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related