如何查询空对象的json列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24292575/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to query a json column for empty objects?
提问by sbeam
Looking to find all rows where a certain json column contains an empty object, {}. This is possible with JSON arrays, or if I am looking for a specific key in the object. But I just want to know if the object is empty. Can't seem to find an operator that will do this.
希望找到某个 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.
回答by Erwin Brandstetter
There is no equality (or inequality) operatorfor the data type jsonas a whole, because equality is hard to establish. Consider jsonbin Postgres 9.4 or later, where this is possible. More details in this related answer on dba.SE (last chapter):
有没有平等（或等于）运算符的数据类型json作为一个整体，因为平等是很难建立。考虑jsonb在 Postgres 9.4 或更高版本中，这是可能的。有关 dba.SE（最后一章）的相关答案中的更多详细信息：
SELECT DISTINCT json_column ...or ... GROUP BY json_columnfail for the same reason (no equality operator).
SELECT DISTINCT json_column ...或... GROUP BY json_column因相同原因失败（无等式运算符）。
Casting both sides of the expression to textallows =or <>operators, but that's not normally reliable as there are many possible text representations for the sameJSON value.
将表达式的两边都转换为text允许=或<>运算符，但这通常不可靠，因为同一个JSON 值有许多可能的文本表示。
However, for this particular case(empty object) it works just fine:
但是，对于这种特殊情况（空对象），它可以正常工作：
select * from test where foo::text <> '{}'::text;
回答by Pensierinmusica
As of PostgreSQL 9.5 this type of query with JSON data is not possible. On the other hand, I agree it would be very useful and created a request for it:
从 PostgreSQL 9.5 开始，这种类型的 JSON 数据查询是不可能的。另一方面，我同意它会非常有用并为此创建了一个请求：
https://postgresql.uservoice.com/forums/21853-general/suggestions/12305481-check-if-json-is-empty
https://postgresql.uservoice.com/forums/21853-general/suggestions/12305481-check-if-json-is-empty
Feel free to vote it, and hopefully it will be implemented!
随意投票，希望它会得到实施！
回答by weinerk
Empty JSON array []could also be relevant.
空 JSON 数组[]也可能相关。
Then this could work for both []and {}:
那么这可以同时适用于[]和{}：
select * from test where length(foo::text) > 2 ;
回答by Clodoaldo Neto
In 9.3 it is possible to count the pairs in each object and filter the ones with none
在 9.3 中，可以计算每个对象中的对并过滤没有的对
create table test (foo json);
insert into test (foo) values
('{"a":1, "c":2}'), ('{"b":1}'), ('{}');
select *
from test
where (select count(*) from json_each(foo) s) = 0;
foo
-----
{}
or test the existence, probably faster for big objects
或测试存在性，对于大物体可能更快
select *
from test
where not exists (select 1 from json_each(foo) s);
Both techniques will work flawlessly regardless of formating
无论格式如何，这两种技术都可以完美运行
回答by David Dehghan
You have to be careful. Casting all your data as a different type so you can compare it will have performance issues on a large database.
你必须要小心。将所有数据转换为不同类型，以便您可以比较它在大型数据库上会出现性能问题。
If your data has a consistent key then you can look for the existence of the key. For example if plan data is {} or {id: '1'}
如果您的数据具有一致的键，那么您可以查找该键是否存在。例如，如果计划数据是 {} 或 {id: '1'}
then you can look for items without 'id'
然后你可以寻找没有“id”的项目
SELECT * FROM public."user"
where NOT(plan ? 'id')
