Postgres - convert single table in grouped JSON tree

Question

I have a table with this data

lvl1           lvl2             lvl3         item
----           ----             ----         ----
a0             b0               c0           1
a0             b0               c1           2
a0             b0               c1           3
a0             b1               c2           4
a1             b1               c3           5
a1             b2               c0           6

How can I convert it in an object tree like this?

[
  {
    id: 'a0', 
    children: [
      {
        id: 'b0',
        children: [
          {
            id: 'c0',
            items: [1]
          },
          {
            id: 'c1',
            items: [2, 3]
          }
        ]
      },
      {
        id: 'b1',
        children: [
          {
            id: 'c2',
            items: [4]
          }
        ]
      }
    ]
  },
  {
    id: 'a1', 
    children: [
      {
        id: 'b1',
        children: [
          {
            id: 'c3',
            items: [5]
          }
        ]
      },
      {
        id: 'b2',
        children: [
          {
            id: 'c0',
            items: [6]
          }
        ]
      }
    ]
  }
]

klin · Accepted Answer · 2018-04-09 22:51:06Z

1

In a sense, the structure of the query is similar to the result:

select json_agg(children)
from (
    select 
        json_build_object(
            'id', lvl1, 
            'children', json_agg(children order by lvl1)) as children
    from (
        select 
            lvl1, 
            json_build_object(
                'id', lvl2, 
                'children', json_agg(items order by lvl2)) as children
        from (
            select 
                lvl1, 
                lvl2, 
                json_build_object(
                    'id', lvl3, 
                    'items', json_agg(item order by lvl3)) as items
            from my_table
            group by lvl1, lvl2, lvl3
            ) s
        group by lvl1, lvl2
        ) s
    group by lvl1
    ) s;

DbFiddle.

Note, that order by in the aggregates are not necessary as the order of a json array is undefined. I've added them to get exactly the expected result.

edited Apr 9, 2018 at 22:51

answered Apr 9, 2018 at 22:29

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Eduardo Over a year ago

What does order by do inside json_agg?

klin Over a year ago

ORDER BY in an aggregate function defines the order of the elements in the aggregated result, read about it in the documentation. You can also try to remove them and see the result. Note that the obtained json objects are equivalent.

Collectives™ on Stack Overflow

Postgres - convert single table in grouped JSON tree

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related