如何查询JSON列中的空对象? [英] How to query a json column for empty objects?

问题描述

试图查找其中某个json列包含空对象{}的所有行.对于JSON数组，或者在对象中寻找特定键，这是可能的.但是我只想知道对象是否为空.似乎找不到要执行此操作的运算符.

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.

 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.

推荐答案

对于整个数据类型 json ，没有没有相等(或不相等)运算符 ，因为很难建立平等.考虑Postgres 9.4中的 jsonb 或稍后，如果可能的话.有关dba.SE(上一章)的相关答案的更多详细信息:

There is no equality (or inequality) operator for the data type json as a whole, because equality is hard to establish. Consider jsonb in Postgres 9.4 or later, where this is possible. More details in this related answer on dba.SE (last chapter):

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

SELECT DISTINCT json_column ...或... GROUP BY json_column由于相同的原因而失败(没有相等运算符).

SELECT DISTINCT json_column ... or ... GROUP BY json_column fail for the same reason (no equality operator).

将表达式的两边都铸造为text允许使用=或<>运算符，但这通常不可靠，因为 same json值有许多可能的文本表示形式.

Casting both sides of the expression to text allows = or <> operators, but that's not normally reliable as there are many possible text representations for the same json value.

但是，对于这种特殊情况(空对象)，它可以正常工作:

However, for this particular case (empty object) it works just fine:

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

这篇关于如何查询JSON列中的空对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！