时间戳

首页 > 解决方案 > 在递归查询中对结果进行排序

sql - 在递归查询中对结果进行排序

问题描述

我有一个基本的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 字段中的排序值并按它排序，但它不起作用。

标签： sqljsonpostgresqlrecursive-queryjsonb

解决方案

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

...
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
)
...

推荐阅读