如何查询空对象的 json 列？

Question

寻找某个 json 列包含空对象的所有行 {} 。这对于 JSON 数组是可能的，或者如果我正在寻找对象中的特定键。但我只想知道对象是否为空。似乎找不到可以执行此操作的操作员。

  dev=# \d test
     Table "public.test"
  Column | Type | Modifiers
 --------+------+-----------
  foo    | json |

 dev=# select * from test;
    foo
 ---------
  {"a":1}
  {"b":1}
  {}
 (3 rows)

 dev=# select * from test where foo != '{}';
 ERROR:  operator does not exist: json <> unknown
 LINE 1: select * from test where foo != '{}';
                                      ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
 dev=# select * from test where foo != to_json('{}'::text);
 ERROR:  operator does not exist: json <> json
 LINE 1: select * from test where foo != to_json('{}'::text);
                                      ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
 dwv=# select * from test where foo != '{}'::json;
 ERROR:  operator does not exist: json <> json
 LINE 1: select * from test where foo != '{}'::json;
                                      ^
 HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

原文由 sbeam 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

数据类型 json 作为一个整体 没有相等（或不相等）运算符，因为相等很难建立。考虑 jsonb 在 Postgres 9.4 或更高版本中，这是可能的。有关 dba.SE 的相关答案中的更多详细信息（最后一章）：

• 如何从 PostgreSQL 中的 JSON[] 数组中删除已知元素？

SELECT DISTINCT json_column ... 或 ... GROUP BY json_column 由于同样的原因（没有相等运算符）失败。

将表达式的两边都转换为 text 允许 = 或 <> 运算符，但这通常不可靠，因为对于 JSON 值有许多 相同 的可能文本表示形式。在 Postgres 9.4 或更高版本中，改为 jsonb 。 （或使用 jsonb 开始。）

但是，对于 这种特殊情况（ 空对象），它工作得很好：

 select * from test where foo::text <> '{}'::text;

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

你必须要小心。将所有数据转换为不同的类型以便您可以比较它在大型数据库上会出现性能问题。

如果您的数据具有一致的密钥，那么您可以查找密钥的存在。例如，如果计划数据是 {} 或 {id: ‘1’}

然后您可以查找没有“id”的项目

SELECT * FROM public."user"
where NOT(plan ? 'id')

原文由 David Dehghan 发布，翻译遵循 CC BY-SA 4.0 许可协议