sql - 递归查询中的排序结果

Question

我有一个类似CATEGORIES的基本表，由primary_key、parent_id、title等字段组成和一个排序整数。

我能够使用 CTE 检索结果并将它们转换为 json 数组，但我想根据排序值获取它们，除了 parent_id。

到目前为止:

with recursive parents as
(
    select n.boat_type_id, n.title, '{}'::int[] as parents, 0 as level
    from boat_types n
    where n.parent_id is NULL
    union all
    select n.boat_type_id, n.title, parents || n.parent_id, level+1
    from parents p
        join boat_types n on n.parent_id = p.boat_type_id
    where not n.boat_type_id = any(parents)
),
children as
(
    select n.parent_id, json_agg(jsonb_build_object('title', n.title->>'en'))::jsonb as js
    from parents tree
        join boat_types n using(boat_type_id)
    where level > 0 and not boat_type_id = any(parents)
    group by n.parent_id
    union all
    select n.parent_id, jsonb_build_object('category', n.title->>'en') || jsonb_build_object('subcategories', js) as js
    from children tree
        join boat_types n on n.boat_type_id = tree.parent_id
)
select jsonb_agg(js) as categories
from children
where parent_id is null  

上面为我提供了我想要的结果集和结构，但是我怎样才能让它们遵循节点和叶子的排序值。

示例响应:

[
   {
      "sorting":0,
      "category":"Motor",
      "subcategories":[
         {
            "title":"Motor Yacht",
            "sorting":2
         },
         {
            "title":"Mega Yacht",
            "sorting":1
         }
      ]
   },
   {
      "sorting":1,
      "category":"Sailing",
      "subcategories":[
         {
            "title":"Sailing Yacht",
            "sorting":2
         },
         {
            "title":"Cruiser Racer",
            "sorting":1
         }
      ]
   },
   {
      "sorting":2,
      "category":"Catamaran",
      "subcategories":[
         {
            "title":"Catamaran",
            "sorting":2
         },
         {
            "title":"Trimaran",
            "sorting":1
         }
      ]
   },
   {
      "sorting":3,
      "category":"Other",
      "subcategories":[
         {
            "title":"Other",
            "sorting":2
         },
         {
            "title":"Airboat",
            "sorting":1
         }
      ]
   }
]

我试过聚合 ARRAY 字段中的排序值并按它排序，但它不起作用。

Answer 1

您可以在 json_agg() 聚合中使用 order by 子句:

...
children as
(
    select 
        n.parent_id, 
        json_agg(jsonb_build_object('title', n.title->>'en', 'sorting', n.sorting) order by n.sorting)::jsonb as js
    from parents tree
        join boat_types n using(boat_type_id)
    where level > 0 and not boat_type_id = any(parents)
    group by n.parent_id
    union all
    select 
        n.parent_id, 
        jsonb_build_object('category', n.title->>'en', 'sorting', n.sorting) || jsonb_build_object('subcategories', js) as js
    from children tree
        join boat_types n on n.boat_type_id = tree.parent_id
)
...