postgresql 返回json单个SQL查询中的层次结构

s5a0g9ez 于 2023-10-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(138)

我需要创建一个SQL查询（postgres 9.5.3），它返回一个分层的JSON结果。这是我目前为止写的代码

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时遇到问题。对于这些结构

array_append函数似乎将任何子元素附加到单个数组中：

A.Children = [ {B}, {C}, {D} ]    //for category A

我需要结构：

A.Children = [ {B, Children = [ {C, Children = [ {D} ] } ] } ]

如何更改查询以实现此目的？问候

postgresql

来源：https://stackoverflow.com/questions/37882614/return-json-hierarchical-structure-in-single-sql-query

2条答案

按热度按时间

9wbgstp71#

我不确定这是可能的，至少在简单方便的方式。
然而，使用“真正的”递归似乎很简单。
下面是一个简单的例子：

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
    case 
      when count(x) > 0 then jsonb_build_object('name', t.name, 'family', jsonb_agg(f.x))
      else jsonb_build_object('name', t.name)
    end
  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 jsonb_pretty(build_family) from build_family(null::int);

and结果

┌──────────────────────────────────────┐
│             jsonb_pretty             │
├──────────────────────────────────────┤
│ {                                   ↵│
│     "name": "john",                 ↵│
│     "family": [                     ↵│
│         {                           ↵│
│             "name": "jane",         ↵│
│             "family": [             ↵│
│                 {                   ↵│
│                     "name": "julian"↵│
│                 }                   ↵│
│             ]                       ↵│
│         },                          ↵│
│         {                           ↵│
│             "name": "jack"          ↵│
│         }                           ↵│
│     ]                               ↵│
│ }                                    │
└──────────────────────────────────────┘

我希望你能适应你的数据。
祝你好运

赞(0）回复(0）举报 2023-10-18

0mkxixxg2#

我也遇到了同样的问题，但是是整排的。在解决这个问题时，我发现了一个优化版本的想法：

create table fam(id int, parent int, name text) ;

insert into fam 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', fam.name)
         ||jsonb_strip_nulls(
                  jsonb_build_object('family', (select jsonb_agg(build_family) from build_family(fam.id)))
            )
  from fam 
  where fam.parent = p_parent or (p_parent is null and fam.parent is null);

$$ language sql;

select jsonb_pretty(build_family) from build_family(null::int);

测试结果：

{
    "name": "john",
    "family": [
        {
            "name": "jane",
            "family": [
                {
                    "name": "julian"
                }
            ]
        },
        {
            "name": "jack"
        }
    ]
}

赞(0）回复(0）举报 2023-10-18

我来回答

postgresql 返回json单个SQL查询中的层次结构

2条答案

相关问题

热门标签

最新问答