如何反向查询分类记录，返回其所有上级父级层级结构？

假设存在如下数据库结构与数据：

create schema if not exists my_schema;

CREATE TABLE IF NOT EXISTS my_schema.category (
    id serial PRIMARY KEY,
    category_name VARCHAR (255) NOT NULL,
    subcategories BIGINT[] DEFAULT ARRAY[]::BIGINT[]
);

INSERT INTO my_schema.category VALUES
    (
        1,
        'Pickup/dropoff',
        '{}'
    ),
    (
        2,
        'Electrician',
        '{}'
    ),
    (
        3,
        'Around the house',
        '{2}'
    ),
    (
        4,
        'Personal',
        '{3}'
    );

需要实现面包屑功能，例如选中Electrician（id=2）分类时，渲染出Personal > Around the house > Electrician的层级路径。以下是针对三种期望格式的SQL实现方案：

1. 返回字符串拼接格式的面包屑 #

WITH RECURSIVE category_path AS (
    SELECT id, category_name, 0 AS level
    FROM my_schema.category
    WHERE id = 2 -- 替换为目标分类ID
    UNION ALL
    SELECT c.id, c.category_name, cp.level + 1
    FROM category_path cp
    JOIN my_schema.category c ON cp.id = ANY(c.subcategories)
)
SELECT 
    2 AS id,
    STRING_AGG(category_name, '.' ORDER BY level DESC) AS breadcrumbs
FROM category_path;

查询结果示例：

{
    "id": 2,
    "breadcrumbs": "Personal.Around the house.Electrician"
}

2. 返回包含父级列表的格式 #

WITH RECURSIVE category_path AS (
    SELECT id, category_name, 0 AS level
    FROM my_schema.category
    WHERE id = 2 -- 替换为目标分类ID
    UNION ALL
    SELECT c.id, c.category_name, cp.level + 1
    FROM category_path cp
    JOIN my_schema.category c ON cp.id = ANY(c.subcategories)
)
SELECT 
    (SELECT id FROM my_schema.category WHERE id = 2) AS id,
    (SELECT category_name FROM my_schema.category WHERE id = 2) AS category_name,
    ARRAY_AGG(
        JSON_BUILD_OBJECT('id', id, 'category_name', category_name)
        ORDER BY level DESC
    ) FILTER (WHERE level > 0) AS breadcrumbs
FROM category_path;

查询结果示例：

{
    "id": 2,
    "category_name": "Electrician",
    "breadcrumbs": [
        {
            "id": 4,
            "category_name": "Personal"
        },
        {
            "id": 3,
            "category_name": "Around the house"
        }
    ]
}

3. 返回完整路径的对象列表格式 #

WITH RECURSIVE category_path AS (
    SELECT id, category_name, 0 AS level
    FROM my_schema.category
    WHERE id = 2 -- 替换为目标分类ID
    UNION ALL
    SELECT c.id, c.category_name, cp.level + 1
    FROM category_path cp
    JOIN my_schema.category c ON cp.id = ANY(c.subcategories)
)
SELECT 
    ARRAY_AGG(
        JSON_BUILD_OBJECT('id', id, 'category_name', category_name)
        ORDER BY level DESC
    ) AS breadcrumbs
FROM category_path;

查询结果示例：

{
    "breadcrumbs": [
        {
            "id": 4,
            "category_name": "Personal"
        },
        {
            "id": 3,
            "category_name": "Around the house"
        },
        {
            "id": 2,
            "category_name": "Electrician"
        }
    ]
}

内容的提问来源于stack exchange，提问作者Mike K