Postgres-在分组的JSON树中转换单个表 [英] Postgres - convert single table in grouped JSON tree

问题描述

我有一张具有此数据的表

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]
          }
        ]
      }
    ]
  }
]

推荐答案

从某种意义上说，查询的结构是类似于结果：

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。

请注意，按排序不需要，因为json数组的顺序未定义。我添加了它们以获得确切的预期结果。

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.

这篇关于Postgres-在分组的JSON树中转换单个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！