Postgres jsonb表同文件夹层级多值查询SQL简化咨询

数据结构 #

表包含主键id和jsonb类型列data，data下有对象数组instances，每个instance包含业务字段与path数组。path是嵌套树形结构的扁平化表示，每个元素是包含非唯一字符串id、层级唯一整数index的对象。

示例：

"instances": [
    {
        "path": [
            {"id": "root", "index": 2},
            {"id": "folder1", "index": 0},
            {"id": "folder2", "index": 0},
            {"id": "folder3", "index": 0}
        ],
        "pdf": "pdf in 1,2,3",
        "info": "some other data"
    },
    ...
]

需求 #

在表中搜索同一指定文件夹层级下同时包含多个特定值的条目。比如查找同一folder2层级（同一父结构下的folder2）中同时存在"pdf in 1,2,3"和"jpg in 1,2,3"的记录。

现有查询语句 #

现有查询完全满足需求，但代码过于冗长：

WITH indexed_paths AS ( 
    SELECT id, instance -> 'index' as inst_idx,
    MIN(CASE WHEN path_element @> '{"id": "folder2"}' THEN path_idx END)
        OVER (PARTITION BY id, instance -> 'index') AS searched_index,
    path_idx, path_element, instance
    FROM "flat",
         jsonb_array_elements(data -> 'instances') AS instance,
         jsonb_array_elements(instance -> 'path') WITH ORDINALITY arr(path_element, path_idx)
    WHERE
    instance -> 'path' @> '[{"id": "folder2"}]'
    ORDER BY id, inst_idx, path_idx
), combined_paths AS ( 
    SELECT id, jsonb_agg(path_element) as path, instance
    FROM indexed_paths
    WHERE path_idx <= searched_index
    GROUP BY id, inst_idx, instance
), combined_instances AS ( 
    SELECT id, path, jsonb_agg(instance) as instances
    FROM combined_paths
    GROUP BY id, path
)
SELECT * 
FROM "flat" f
WHERE EXISTS (
    SELECT 1
    FROM combined_instances ci
    WHERE
        ci.id = f.id
        AND ci.instances @> '[{"pdf": "pdf in 1,2,3"}, {"jpg": "jpg in 1,2,3"}]'::jsonb
);

查询逻辑 #

1. indexed_paths：展开instances和每个instance的path，标记目标folder2在path中的最小索引
2. combined_paths：按记录和实例分组，重构出到folder2为止的路径
3. combined_instances：按记录和folder2路径分组，聚合同层级的所有实例
4. 最终筛选出同层级实例包含指定值的原表记录

简化方案 #

方案1：不修改数据结构，精简查询语句 #

将原有的三个CTE合并为一个，直接在子查询中提取目标层级路径，减少冗余步骤：

WITH folder2_groups AS (
  SELECT
    id,
    -- 提取path中到第一个"folder2"为止的元素组成数组，作为分组标识
    (SELECT jsonb_agg(elem)
     FROM jsonb_array_elements(instance->'path') WITH ORDINALITY arr(elem, idx)
     WHERE idx <= (SELECT MIN(idx)
                  FROM jsonb_array_elements(instance->'path') WITH ORDINALITY arr(e, idx)
                  WHERE e @> '{"id": "folder2"}')) AS folder2_path,
    instance
  FROM flat,
       jsonb_array_elements(data->'instances') AS instance
  WHERE instance->'path' @> '[{"id": "folder2"}]'
)
SELECT f.*
FROM flat f
WHERE EXISTS (
  SELECT 1
  FROM folder2_groups g
  WHERE g.id = f.id
  GROUP BY g.id, g.folder2_path
  HAVING jsonb_agg(g.instance) @> '[{"pdf": "pdf in 1,2,3"}, {"jpg": "jpg in 1,2,3"}]'::jsonb
);

方案2：调整数据结构，优化查询效率 #

如果允许修改data结构，建议在每个instance中预先存储目标层级的唯一标识（比如路径的字符串拼接值），避免每次查询都解析path数组：

1. 修改instance结构，新增folder2_key字段，值为到folder2的路径拼接字符串（如root:2/folder1:0/folder2:0）
2. 查询时直接按这个字段分组，代码更简洁且性能更高：

SELECT f.*
FROM flat f
WHERE EXISTS (
  SELECT 1
  FROM flat,
       jsonb_array_elements(data->'instances') AS instance
  WHERE id = f.id
    AND instance->>'folder2_key' IS NOT NULL
  GROUP BY id, instance->>'folder2_key'
  HAVING jsonb_agg(instance) @> '[{"pdf": "pdf in 1,2,3"}, {"jpg": "jpg in 1,2,3"}]'::jsonb
);

还可以给data->'instances'->>'folder2_key'创建GIN索引，进一步提升分组和筛选的速度。

内容的提问来源于stack exchange，提问作者George Bagiryan