Postgres-将邻接表转换为嵌套的JSON对象 [英] Postgres - Convert adjacency list to nested JSON object
问题描述
我在Postgres中有一个包含此数据的表，我很难将其转换为JSON对象.
I have a table with this data in Postgres and I am having a hard time to convert this in to a JSON object.
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: []
}
]
},
...
]
}
任何建议都会有所帮助.谢谢
Any suggestion will help. Thanks
推荐答案
使用WITH RECURSIVE( https://www.postgresql.org/docs/current/static/queries-with.html )和JSON函数(
Using WITH RECURSIVE (https://www.postgresql.org/docs/current/static/queries-with.html) and JSON Functions (https://www.postgresql.org/docs/current/static/functions-json.html) I build this solution:
核心功能:
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组合.
Every WITH RECURSIVE contains a start SELECT and a recursion part (the second SELECT) combined by a UNION.
A:再次连接表本身以查找node_id的子级.
A: Joining the table agains itself for finding the children of a node_id.
B:为孩子构建可插入其父对象的json对象
B: Building the json object for the child which can be inserted into its parent
C:构建必须在其中插入子对象的路径(从根目录开始).窗口函数row_number()( https://www.postgresql.org /docs/current/static/tutorial-window.html )生成父级的childs数组中的子级索引.
C: Building the path where the child object has to be inserted (from root). The window function row_number() (https://www.postgresql.org/docs/current/static/tutorial-window.html) generates the index of the child within the children array of the parent.
D:递归部分作为初始部分起作用，但有一个区别:它不是在搜索根元素，而是在搜索具有上一个递归的父节点的元素.
D: The recursion part works as the initial part with one difference: It's not searching for the root element but for the element which has the parent node of the last recursion.
E:执行递归并过滤所有没有任何子元素的元素会得到以下结果:
E: Executing the recursion and filtering all elements without any children gives this result:
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没有全局变量；所以它始终知道直接祖先的更改，而不是其兄弟姐妹的更改)，但我不得不在几秒钟内迭代这些行步骤.
Though I found no way to add all children elements in the recursion (the origin json is no global variable; so it always knows the changes of the direct ancestors, not their siblings), I had to iterate the rows in a seconds step.
这就是为什么我构建函数的原因.在这里，我可以对全局变量进行迭代.借助功能jsonb_insert，我将使用计算出的路径将所有计算出的元素插入到json根对象中.
That's why I build the function. In there I can do the iteration for a global variable. With the function jsonb_insert I am inserting all calculated elements into a root json object - using the calculated path.
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())
Last step is calling the function and make the JSON more readable (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": []
}]
}]
}
我确信可以优化查询，但是对于草图它是可行的.
I am sure it is possible to optimize the query but for a sketch it works.
这篇关于Postgres-将邻接表转换为嵌套的JSON对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
