如何查询空对象的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没有没有相等(或不等)运算符强>作为一个整体，因为平等很难建立.考虑 jsonb 在 Postgres 9.4 或更高版本中，这是可能的.有关 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 允许 = 或 <> 运算符，但这通常不可靠，因为有很多相同 JSON 值的可能文本表示.在 Postgres 9.4 或更高版本中，改为转换为 jsonb.(或使用 jsonb 开始.)

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. In Postgres 9.4 or later, cast to jsonb instead. (Or use jsonb to begin with.)

但是，对于这种特殊情况(空对象)它工作得很好:

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

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

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