Postgres-将邻接表转换为嵌套的JSON对象

Question

我在Postgres中有一个包含此数据的表，并且很难将其转换为JSON对象。

node_id    parent_node    name
-------    -----------    ----
1                         node1
2          1              node2
3          1              node3
4          2              node4
5          2              node5
6          2              node6
7          3              node7
8          3              node8

我该如何转换呢？

{
    name: 'node1'
    childs: [
        { 
            name: 'node2',
            childs: [
                {
                    name: 'node4',
                    childs: []
                },
                {
                    name: 'node5',
                    childs: []
                },
                {
                    name: 'node6',
                    childs: []
                }
            ]
        },
        ...    
    ]
}

任何建议都会有所帮助。谢谢

Answer 1

使用WITH RECURSIVE（https://www.postgresql.org/docs/current/static/queries-
with.html
）和JSON函数（https://www.postgresql.org/docs/current/static/functions-
json.html
）我建立这个解决方案：

db
<>小提琴

核心功能：

    WITH RECURSIVE tree(node_id, ancestor, child, path, json) AS  (
      SELECT 
          t1.node_id, 
          NULL::int, 
          t2.node_id,
          '{children}'::text[] || 
             (row_number() OVER (PARTITION BY t1.node_id ORDER BY t2.node_id) - 1)::text,-- C
          jsonb_build_object('name', t2.name, 'children', array_to_json(ARRAY[]::int[])) -- B
      FROM test t1
      LEFT JOIN test t2 ON t1.node_id = t2.parent_node                                   -- A
      WHERE t1.parent_node IS NULL

      UNION

      SELECT
          t1.node_id, 
          t1.parent_node, 
          t2.node_id,
          tree.path || '{children}' || (row_number() OVER (PARTITION BY t1.node_id ORDER BY t2.node_id) - 1)::text, 
          jsonb_build_object('name', t2.name, 'children', array_to_json(ARRAY[]::int[]))
      FROM test t1
      LEFT JOIN test t2 ON t1.node_id = t2.parent_node
      INNER JOIN tree ON (t1.node_id = tree.child)
      WHERE t1.parent_node = tree.node_id                                                -- D
    )
    SELECT                                                                               -- E
        child as node_id, path, json 
    FROM tree 
    WHERE child IS NOT NULL ORDER BY path

每个元素都WITH RECURSIVE包含一个开始SELECT部分和一个递归部分（第二个部分SELECT），并由组合UNION。

答：再次参加餐桌本身就可以找到的孩子node_id。

B：为孩子建立json对象，可以将其插入其父对象

C：构建必须在其中插入子对象的路径（从根目录开始）。窗口函数row_number()（https://www.postgresql.org/docs/current/static/tutorial-
window.html）生成父级子级数组中子级的索引。

D：递归部分作为初始部分起作用，但有一个区别：它不是在搜索根元素，而是在搜索具有上一个递归的父节点的元素。

E：执行递归并过滤所有元素而没有任何子元素将得到以下结果：

node_id   path                      json
2         children,0                {"name": "node2", "children": []}
4         children,0,children,0     {"name": "node4", "children": []}
5         children,0,children,1     {"name": "node5", "children": []}
6         children,0,children,2     {"name": "node6", "children": []}
3         children,1                {"name": "node3", "children": []}
7         children,1,children,0     {"name": "node7", "children": []}
8         children,1,children,1     {"name": "node8", "children": []}

尽管我找不到在递归中添加所有子元素的方法（origin
json不是全局变量；所以它始终知道直接祖先的更改，而不是其兄弟姐妹的更改），但我不得不在几秒钟的时间内迭代这些行。

这就是为什么我构建函数。在这里，我可以对全局变量进行迭代。使用该功能，jsonb_insert我将所有计算出的元素插入到根json对象中-
使用计算出的路径。

CREATE OR REPLACE FUNCTION json_tree() RETURNS jsonb AS $$
DECLARE
    _json_output jsonb;
    _temprow record;
BEGIN
    SELECT 
        jsonb_build_object('name', name, 'children', array_to_json(ARRAY[]::int[])) 
    INTO _json_output 
    FROM test 
    WHERE parent_node IS NULL;

    FOR _temprow IN
        /* Query above */
    LOOP
        SELECT jsonb_insert(_json_output, _temprow.path, _temprow.json) INTO _json_output;
    END LOOP;

    RETURN _json_output;
END;
$$ LANGUAGE plpgsql;

最后一步是调用函数，并使JSON更具可读性（jsonb_pretty()）

{
    "name": "node1",
    "children": [{
        "name": "node2",
        "children": [{
            "name": "node4",
            "children": []
        },
        {
            "name": "node5",
            "children": []
        },
        {
            "name": "node6",
            "children": []
        }]
    },
    {
        "name": "node3",
        "children": [{
            "name": "node7",
            "children": []
        },
        {
            "name": "node8",
            "children": []
        }]
    }]
}

我敢肯定有可能优化查询，但是对于草图它是可行的。