PostgreSQL中查询对象数组内缺失指定键的JSON数据

你的原查询无法得到预期结果，核心问题有两个：

• WHERE子句不能直接引用SELECT里定义的partyGroup别名——SQL执行顺序是先处理WHERE再处理SELECT，此时partyGroup还未生成。
• 原查询会把数组拆分为单行返回缺失name的元素，但你需要的是整条包含这类数组的notice记录，而非拆分后的行。

以下是两种可行的解决方案：

方法1：使用EXISTS子查询（推荐，性能更优） #

SELECT id, json_message
FROM "notice" n
WHERE EXISTS (
    SELECT 1
    FROM JSONB_ARRAY_ELEMENTS(n.json_message->'payload'->'groups') AS elem
    WHERE NOT (elem ? 'name')
);

这个查询会检查每条notice记录的groups数组，只要数组内存在任意一个没有"name"键的对象，就返回整条记录。

方法2：使用JSONB_PATH_EXISTS（PostgreSQL 12及以上版本支持） #

SELECT id, json_message
FROM "notice"
WHERE JSONB_PATH_EXISTS(
    json_message,
    '$.payload.groups[*] ? (!exists(@.name))'
);

通过JSON路径表达式直接匹配数组中缺失"name"键的元素，语法更简洁直观。

如果仅需查看具体哪些数组元素缺失了"name"键，可以用子查询先拆分数组再筛选：

SELECT id, partyGroup
FROM (
    SELECT id, JSONB_ARRAY_ELEMENTS(json_message->'payload'->'groups') AS partyGroup
    FROM "notice"
) AS sub_query
WHERE NOT (partyGroup ? 'name');

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