递归父/子JSON Postgresql

Question

我有以下递归PostgreSQL表:

CREATE TABLE public."data" (
id int4 NULL,
parentid int4 NULL,
menu varchar NULL
);

我试图从表中创建一个JSON对象。

[
{
"menu": "home",
"children": [
{
"menu": "home 1",
"children": []
},
{
"menu": "home 2",
"children": [
{
"menu": "home 2.1",
"children": []
}
]
}
]
},
{
"menu": "config",
"children": []
}
]

我怎么能像这样创建一个层次对象?

Answer 1

首先，您应该使用jsonb格式而不是postgres中的json格式，请参阅此处的文档:

一般来说，大多数应用程序应该倾向于将JSON数据存储为jsonb,除非有特殊需要,如遗产关于对象键排序的假设…

然后，假设您的表是一组(id, menu)元组，parentid是该元组的父元组的id，您可以尝试:

CREATE VIEW parent_children (parent, children, root, cond) AS
(   SELECT jsonb_build_object('menu', p.menu, 'children', '[]' :: jsonb) :: text AS parent
, jsonb_agg(jsonb_build_object('menu', c.menu, 'children', '[]' :: jsonb)) :: text AS children
, array[c.parentid] AS root
, array[c.parentid] AS cond
FROM public.data AS c
LEFT JOIN public.data AS p
ON p.id = c.parentid
GROUP BY c.parentid
) ;
WITH RECURSIVE list(parent, children, root, cond) AS
(   SELECT parent, children, root, cond
FROM parent_children
WHERE root = array[NULL]   -- start with the root parents
UNION
SELECT p.parent
, replace(p.children, c.parent, replace(c.parent, '[]', c.children))
, p.root
, p.cond || c.cond
FROM list AS p
INNER JOIN parent_children AS c
ON position(c.parent IN p.children) > 0
AND NOT p.cond @> c.root -- condition to avoid circular path
)
SELECT children :: jsonb
FROM list AS l
ORDER BY array_length(cond, 1) DESC
LIMIT 1 ;