使用Postgres将列表邻接表转换为JSON图 [英] Adjacency List to JSON graph with Postgres
问题描述
标签表具有以下架构:
CREATE TABLE tags (
id integer NOT NULL,
name character varying(255) NOT NULL,
parent_id integer
);
我需要构建一个查询以返回以下结构(为便于阅读,此处以yaml表示):
I need to build a query to return the following structure (here represented as yaml for readability):
- name: Ciencia
parent_id:
id: 7
children:
- name: Química
parent_id: 7
id: 9
children: []
- name: Biología
parent_id: 7
id: 8
children:
- name: Botánica
parent_id: 8
id: 19
children: []
- name: Etología
parent_id: 8
id: 18
children: []
经过反复试验并在SO中寻找类似的问题,我想到了以下查询:
After some trial and error and looking for similar questions in SO, I've came up with this query:
WITH RECURSIVE tagtree AS (
SELECT tags.name, tags.parent_id, tags.id, json '[]' children
FROM tags
WHERE NOT EXISTS (SELECT 1 FROM tags tt WHERE tt.parent_id = tags.id)
UNION ALL
SELECT (tags).name, (tags).parent_id, (tags).id, array_to_json(array_agg(tagtree)) children FROM (
SELECT tags, tagtree
FROM tagtree
JOIN tags ON tagtree.parent_id = tags.id
) v
GROUP BY v.tags
)
SELECT array_to_json(array_agg(tagtree)) json
FROM tagtree
WHERE parent_id IS NULL
但是当转换为yaml时,它会返回以下结果:
But it returns the following results when converted to yaml:
- name: Ciencia
parent_id:
id: 7
children:
- name: Química
parent_id: 7
id: 9
children: []
- name: Ciencia
parent_id:
id: 7
children:
- name: Biología
parent_id: 7
id: 8
children:
- name: Botánica
parent_id: 8
id: 19
children: []
- name: Etología
parent_id: 8
id: 18
children: []
根节点已重复. 我可以将结果合并到我的应用程序代码中的预期结果中,但是我感觉很亲密,可以通过PG完成.
The root node is duplicated. I could merge the results to the expected result in my app code but I feel I am close and it could be done al from PG.
这是SQL Fiddle的一个示例: http://sqlfiddle.com/#!15/1846e/1/0
Here's an example with SQL Fiddle: http://sqlfiddle.com/#!15/1846e/1/0
预期输出: https://gist.github.com/maca/e7002eb10f36fcdbc51b
实际输出: https://gist.github.com/maca/78e84fb7c05ff23f07f4
推荐答案
以下是使用PLV8作为架构的解决方案.
Here's a solution using PLV8 for your schema.
首先,使用PLSQL函数和递归CTE构建物化路径.
First, build a materialized path using PLSQL function and recursive CTEs.
CREATE OR REPLACE FUNCTION get_children(tag_id integer)
RETURNS json AS $$
DECLARE
result json;
BEGIN
SELECT array_to_json(array_agg(row_to_json(t))) INTO result
FROM (
WITH RECURSIVE tree AS (
SELECT id, name, ARRAY[]::INTEGER[] AS ancestors
FROM tags WHERE parent_id IS NULL
UNION ALL
SELECT tags.id, tags.name, tree.ancestors || tags.parent_id
FROM tags, tree
WHERE tags.parent_id = tree.id
) SELECT id, name, ARRAY[]::INTEGER[] AS children FROM tree WHERE $1 = tree.ancestors[array_upper(tree.ancestors,1)]
) t;
RETURN result;
END;
$$ LANGUAGE plpgsql;
然后,从上述函数的输出中构建树.
Then, build the tree from the output of the above function.
CREATE OR REPLACE FUNCTION get_tree(data json) RETURNS json AS $$
var root = [];
for(var i in data) {
build_tree(data[i]['id'], data[i]['name'], data[i]['children']);
}
function build_tree(id, name, children) {
var exists = getObject(root, id);
if(exists) {
exists['children'] = children;
}
else {
root.push({'id': id, 'name': name, 'children': children});
}
}
function getObject(theObject, id) {
var result = null;
if(theObject instanceof Array) {
for(var i = 0; i < theObject.length; i++) {
result = getObject(theObject[i], id);
if (result) {
break;
}
}
}
else
{
for(var prop in theObject) {
if(prop == 'id') {
if(theObject[prop] === id) {
return theObject;
}
}
if(theObject[prop] instanceof Object || theObject[prop] instanceof Array) {
result = getObject(theObject[prop], id);
if (result) {
break;
}
}
}
}
return result;
}
return JSON.stringify(root);
$$ LANGUAGE plv8 IMMUTABLE STRICT;
这将产生您的问题中提到的必需JSON.希望有帮助.
This will yield the required JSON mentioned in your question. Hope that helps.
我已经写了详细的帖子/关于此解决方案的工作方式的详细信息这里.
I've written a detailed post/breakdown of how this solution works here.
这篇关于使用Postgres将列表邻接表转换为JSON图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!