如何用PostgreSQL的CTE递归遍历嵌套JSONB规则并拼接Value

针对多层嵌套的JSONB规则结构，我们可以用PostgreSQL的递归CTE（Common Table Expression）遍历所有层级，拼接路径上的Value字段，最终得到叶子节点的聚合结果。

解决方案代码 #

假设你的表名为rule_table，主键为id，rules是JSONB类型的列，执行以下SQL：

WITH RECURSIVE rule_hierarchy AS (
    -- 种子查询：展开根节点的第一层Rules数组，初始化拼接值
    SELECT
        id,
        jsonb_array_elements(rules->'Rules') AS current_rule,
        (jsonb_array_elements(rules->'Rules')->'RuleProperties'->>'Value') AS current_value
    FROM rule_table
    WHERE rules->'Rules' IS NOT NULL

    UNION ALL

    -- 递归查询：遍历子Rules数组，拼接Value
    SELECT
        rh.id,
        jsonb_array_elements(rh.current_rule->'Rules') AS current_rule,
        rh.current_value || (jsonb_array_elements(rh.current_rule->'Rules')->'RuleProperties'->>'Value') AS current_value
    FROM rule_hierarchy rh
    WHERE rh.current_rule->'Rules' IS NOT NULL
)
-- 筛选出叶子节点（没有子Rules的节点），输出最终拼接结果
SELECT DISTINCT current_value
FROM rule_hierarchy
WHERE current_rule->'Rules' IS NULL;

代码解释 #

1. 种子查询：

   • 用jsonb_array_elements展开根节点下的Rules数组，将每个子规则拆分为单独行
   • 提取每个子规则的RuleProperties->Value作为初始拼接值

2. 递归查询：

   • 对上一轮递归结果中的每个规则节点，继续展开它的Rules子数组
   • 将当前已拼接的current_value与子节点的Value进行字符串拼接，传递到下一层

3. 最终筛选：

   • 只保留Rules为null的叶子节点，这些节点的current_value就是完整的拼接结果

适配特殊情况 #

如果存在某层RuleProperties为null的情况（比如中间节点无Value），可以用coalesce避免拼接null值：

-- 修改递归部分的current_value拼接逻辑
rh.current_value || coalesce((jsonb_array_elements(rh.current_rule->'Rules')->'RuleProperties'->>'Value'), '') AS current_value

测试示例数据 #

如果你的表中有一行示例数据：

INSERT INTO rule_table (id, rules) VALUES (1, '{
    "RuleProperties": null,
    "Rules": [
        {
            "RuleProperties": {
                "field": "pnt_name",
                "label": "pnt_name",
                "operator": "contains",
                "type": "string",
                "Value": "^"
            },
            "Rules": [
                {
                    "RuleProperties": {
                        "field": "pnt_name",
                        "label": "pnt_name",
                        "operator": "contains",
                        "type": "string",
                        "Value": "P"
                    },
                    "Rules": [
                        {
                            "RuleProperties": {
                                "field": "pnt_name",
                                "label": "pnt_name",
                                "operator": "contains",
                                "type": "string",
                                "Value": "SK"
                            },
                            "Rules": null,
                            "XOR": "and"
                        }
                    ],
                    "XOR": "and"
                }
            ],
            "XOR": "and"
        },
        {
            "RuleProperties": {
                "field": "pnt_name",
                "label": "pnt_name",
                "operator": "contains",
                "type": "string",
                "Value": "R"
            },
            "Rules": null,
            "XOR": "and"
        }
    ],
    "XOR": "and"
}'::jsonb);

执行上述递归CTE查询后，将得到结果：

current_value
--------------
^PSK
^R

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