在postgres中创建json树结构

Question

我试图创建一个JSON表示任意深度和广泛的层次结构，例如生物:

CREATE TABLE creatures (
name text PRIMARY KEY,
parent text REFERENCES creatures(name)
);
INSERT INTO creatures(name,parent)
VALUES
('amoeba',NULL),
('beetle','amoeba'),
('coelacanth','amoeba'),
('salmon','coelacanth'),
('tuna','coelacanth'),
('lizard','coelacanth'),
('t-rex','lizard'),
('plant',NULL);

我想把它变成JSON，像这样:

[{"name":"amoeba",
"children": [{"name": "beetle",
"children": []}, 
{"name": "coelacanth",
"children": [{"name": "tuna",
"children": []}, 
{"name": "salmon",
"children": []} 
{"name": "lizard",
"children": [{"name": "t-rex",
"children": []}]}
]}]},
{"name": "plant",
"children": []}]

这在Postgres中是可能的吗?

到目前为止我已经试过了

WITH RECURSIVE r AS
-- Get all the leaf nodes, group by parent.
(SELECT parent,
json_build_object('name', parent, 
'children', array_agg(name)) AS json
FROM creatures c
WHERE parent NOTNULL
AND NOT EXISTS
(SELECT 1
FROM creatures c2
WHERE c.name = c2.parent)
GROUP BY parent

UNION 

-- Recursive term - go one step up towards the top.
SELECT c.parent,
json_build_object('name', c.parent, 
'children', array_agg(c.name)) AS json
FROM r
JOIN creatures c ON r.parent = c.name
GROUP BY c.parent)
SELECT *
FROM r;

但是

就失败了

ERROR:  aggregate functions are not allowed in a recursive query's recursive term
LINE 19:                            'children', array_agg(c.name)) AS...

有没有办法解决这个问题，或者另一个解决方案，可以让我的好树?

Answer 1

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

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

然后，下面是一种将jsonb转换为文本的方法，因为jsonb替换函数jsonb_set在您的情况下是不舒服的:

CREATE VIEW parent_children (parent, children, root, cond) AS
(   SELECT jsonb_build_object('name', c.parent, 'children', '[]' :: jsonb) :: text AS parent
, jsonb_agg(jsonb_build_object('name', c.name, 'children', '[]' :: jsonb)) :: text AS children
, array[c.parent] AS root
, array[c.parent] AS cond
FROM creatures AS c
GROUP BY c.parent
) ;
WITH RECURSIVE list(parent, children, root, cond) AS
(   SELECT children, 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 ;

结果是:

[
{
"name": "amoeba",
"children": [
{
"name": "beetle",
"children": []
},
{
"name": "coelacanth",
"children": [
{
"name": "salmon",
"children": []
},
{
"name": "tuna",
"children": []
},
{
"name": "lizard",
"children": [
{
"name": "t-rex",
"children": []
}
]
}
]
}
]
},
{
"name": "plant",
"children": []
}
]