PostgreSQL 15树形结构路径查询优化及JSONB多语言匹配问询

1. 用递归CTE实现灵活的树形路径查询 #

你当前的固定层级JOIN查询在路径深度变化时需要修改SQL结构，递归CTE可以适配任意深度的树形结构，逻辑更灵活。以下是针对需求实现的递归查询：

场景：给定路径节点名称列表（如['clothes', 'men', 'hosen']），匹配任意语言版本并找到最终节点 #

WITH RECURSIVE taxon_path AS (
    -- 锚点成员：匹配路径第一个节点（根节点，parent_id IS NULL）
    SELECT 
        id, 
        parent_id, 
        name,
        1 AS level
    FROM taxons
    WHERE parent_id IS NULL
        AND EXISTS (
            SELECT 1 
            FROM jsonb_each_text(name) 
            WHERE LOWER(value) = LOWER('clothes')
        )
    UNION ALL
    -- 递归成员：逐级匹配路径的下一个节点
    SELECT 
        t.id, 
        t.parent_id, 
        t.name,
        tp.level + 1 AS level
    FROM taxons t
    JOIN taxon_path tp ON t.parent_id = tp.id
    WHERE EXISTS (
        SELECT 1 
        FROM jsonb_each_text(t.name) 
        WHERE LOWER(value) = LOWER(
            -- 按路径顺序取对应层级的目标名称，数组索引从1开始
            ARRAY['clothes', 'men', 'hosen'][tp.level + 1]
        )
    )
)
-- 取路径的最后一个节点（层级等于路径长度）
SELECT * 
FROM taxon_path
WHERE level = array_length(ARRAY['clothes', 'men', 'hosen'], 1);

优势：

• 支持任意深度的路径查询，仅需调整目标名称数组即可，无需修改SQL结构
• 锚点+递归的结构贴合树形数据的层级关系，逻辑更清晰

2. JSONB字段无需指定所有语言的匹配方式 #

使用jsonb_each_text()函数展开name字段的所有键值对，遍历所有语言版本完成匹配，避免逐个指定语言：

单节点匹配示例（匹配任意语言下值为hosen的节点） #

SELECT *
FROM taxons
WHERE EXISTS (
    SELECT 1
    FROM jsonb_each_text(name)
    WHERE LOWER(value) = LOWER('hosen')
);

性能优化建议 #

如果这类查询频率较高，可创建表达式索引加速匹配：

-- 基于所有语言小写值的GIN索引
CREATE INDEX idx_taxons_name_values_lower ON taxons USING gin (
    (SELECT array_agg(LOWER(value)) FROM jsonb_each_text(name))
);

内容的提问来源于stack exchange，提问作者23tux