返回json单个SQL查询中的分层结构
问题描述:
我需要创建一个返回分层json结果的SQL查询(postgres 9.5.3)。这是我写到目前为止的代码返回json单个SQL查询中的分层结构
WITH RECURSIVE q AS (
WITH c AS (
SELECT pc."Id", pc."Description"
FROM "ProductCategories" pc
WHERE pc."Active" = true
)
SELECT pc, ARRAY[c] as "Children", ARRAY[pc."Id"] as "Path"
FROM "ProductCategories" pc
LEFT JOIN c ON pc."Id" = c."Id"
WHERE NULLIF(pc."ParentId", 0) IS NULL
AND pc."Active" = true
UNION ALL
SELECT pc_descendant, array_append(q."Children", c), q."Path" || pc_descendant."Id"
FROM q
JOIN "ProductCategories" pc_descendant ON pc_descendant."ParentId" = (q.pc)."Id"
LEFT JOIN c ON pc_descendant."Id" = c."Id"
WHERE pc_descendant."Active" = true
)
SELECT * FROM q
我有问题创建分层对象Children
。对于这些结构
A
B
C
D
E
的array_append
功能似乎添加任何孩子元素融入到一个数组:
A.Children = [ {B}, {C}, {D} ] //for category A
我需要的结构:
A.Children = [ {B, Children = [ {C, Children = [ {D} ] } ] } ]
我怎样才能改变我的查询来实现这一? Regards
答
不确定这是可能的,至少在简单方便的方式。
但是,它似乎很简单,使用“真正的”递归。
下面是简单的例子:
create temp table t(id int, parent int, name text) on commit drop;
insert into t values
(1,null,'john'),
(2,1,'jane'),
(3,1,'jack'),
(4,2,'julian');
create or replace function build_family(p_parent int) returns setof jsonb as $$
select jsonb_build_object('name', t.name, 'family', jsonb_agg(f.x))
from t left join build_family(t.id) as f(x) on true
where t.parent = p_parent or (p_parent is null and t.parent is null)
group by t.id, t.name;
$$ language sql;
select * from build_family(null::int);
和结果是
{"name": "john", "family": [{"name": "jane", "family": [{"name": "julian", "family": [null]}]}, {"name": "jack", "family": [null]}]}
我希望你能适应它为您的数据。
祝你好运。