PostgreSQL中查询JSONB数组内指定元素的索引/路径

数据集 #

Table 1
-------
Id | JsonB
1  | [ { "Name": "ABC" }, { "Name": "DEF" } ]
2  | [ { "Name": "XYZ" }, { "Name": "ABC" } ]
3  | [ { "Name": "GHI" }, { "Name": "JKL" } ]

查询需求 #

搜索指定JSONB元素（例如 {"Name": "ABC"}），返回每条记录的Id以及该元素在数组中的索引列表，不存在则返回null或空数组。预期结果如下：

Query result
--------------------
Id | Index
1  | [0]
2  | [1]
3  | null or []

补充说明：由于PostgreSQL采用路径机制处理嵌套查询，返回路径格式的结果也可接受，预期结果如下：

Query result
--------------------
Id | Path
1  | ['{0}']
2  | ['{1}']
3  | null or []

方案1：返回索引列表 #

通过jsonb_array_elements结合WITH ORDINALITY遍历数组元素并获取索引，再聚合结果：

SELECT 
    t.Id,
    CASE 
        WHEN array_agg(i.index) <> '{}' THEN array_agg(i.index)
        ELSE NULL 
    END AS "Index"
FROM 
    "Table 1" t
LEFT JOIN LATERAL (
    SELECT ordinality - 1 AS index
    FROM jsonb_array_elements(t.JsonB) WITH ORDINALITY
    WHERE value = '{"Name": "ABC"}'::jsonb
) i ON true
GROUP BY t.Id
ORDER BY t.Id;

方案2：返回PostgreSQL路径格式 #

使用jsonb_path_query结合路径提取功能，直接获取匹配元素的路径：

SELECT 
    t.Id,
    CASE 
        WHEN array_agg('{' || (j.path::text::int - 1) || '}') <> '{}' 
        THEN array_agg('{' || (j.path::text::int - 1) || '}')
        ELSE NULL 
    END AS "Path"
FROM 
    "Table 1" t
LEFT JOIN LATERAL jsonb_path_query(
    t.JsonB, 
    '$[*] ? (@ == $elem)'::jsonpath, 
    '{"elem": {"Name": "ABC"}}', 
    path => true
) j ON true
GROUP BY t.Id
ORDER BY t.Id;

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