json - postgresql 未定义的 json 字段未按预期返回 null

Question

目标是返回 JSON 数据类型中缺少特定键的所有记录。只是为了说明这一点，我正在尝试查询 JSON 结构以查找 faultyField 在以下表数据中返回 null 的任何内容:

 id::SERIAL |       data::JSON
------------+-------------------
          1 | {"key" : "val"}
          2 | {"key1" : "val2"}

以下两个语句中的任何一个:

SELECT * FROM test WHERE data->>'faultyField' = null;
SELECT * FROM test WHERE (data->>'faultyField')::text = 'null';

应该返回所有值(根据我在工作中发现的两个 SO 帖子)？但它不是出于某种原因。这两个只返回空结果。

我不是 PostgreSQL 的向导，所以我已经尝试了下面这些语句之间的所有内容，只是为了看看它们是否有效。遗憾的是他们没有。

SELECT * FROM test WHERE (SELECT json_object_keys(data) FROM test)='key';
SELECT * FROM test WHERE 'key' in json_object_keys(data);
SELECT * FROM test WHERE 'key' := json_object_keys(data);
SELECT * FROM test WHERE 'key' ANY (json_object_keys(data));

有没有办法返回 JSON 字符串中缺少键的记录？

Answer 1

编辑

我强烈建议您使用 JSONB 而不是 JSON。它是一种更强大的类型，允许索引并具有我在下面演示的其他运算符。

---

如果要测试 jsonb 字段的顶级中是否存在键，请使用 ? operator:

CREATE TEMP TABLE test AS VALUES 
    (1, '{"key" : "val"}'::JSONB),
    (2, '{"key2" : "val2"}'::JSONB);

SELECT * FROM test WHERE NOT column2 ? 'key3';

使用 data->>'faultyField' 将尝试获取该键的值，而不是测试该键是否存在。这不是您要查找的内容，因为它会返回行，即使 faultyField 键存在并且具有空值。