PostgreSQL 9.6.24中JSONB列嵌套数组的状态匹配查询问题

你的查询未返回结果的核心原因是JSON路径错误：metas数组并非直接位于顶层metadata对象下，而是嵌套在items数组元素的metadata字段内，原SQL直接读取metadata->'metas'会得到null，因此无法匹配数据。

以下是两种适配PostgreSQL 9.6.24的解决方案：

方案一：匹配metas数组中任意符合条件的status #

通过两次数组展开（先展开items，再展开每个item下的metas），筛选出任意metas元素status为active或reported的记录：

SELECT b.*
FROM blobs b
WHERE b.metadata IS NOT NULL
AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(b.metadata->'items') AS items_arr
    CROSS JOIN jsonb_array_elements(items_arr->'metadata'->'metas') AS metas_arr
    WHERE metas_arr->>'status' IN ('active', 'reported')
);

方案二：仅匹配metas数组第一个元素的status #

若只需检查每个item下metas数组的第一个元素，可直接通过JSON路径访问，无需展开整个数组：

检查任意item的第一个metas元素 #

SELECT *
FROM blobs
WHERE metadata IS NOT NULL
AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(metadata->'items') AS items_arr
    WHERE (items_arr->'metadata'->'metas'->0->>'status') IN ('active', 'reported')
);

仅检查第一个item的第一个metas元素 #

如果业务场景只关注items数组的第一个元素，可简化为：

SELECT *
FROM blobs
WHERE metadata IS NOT NULL
AND (metadata->'items'->0->'metadata'->'metas'->0->>'status') IN ('active', 'reported');

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