logo标签列表

如何查询JSON列中的空对象？

sqljsonpostgresqltypesjsonb
97

想要查找包含空对象{}的特定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.
9个回答
166

对于整个数据类型json，不存在相等（或不相等）运算符，因为很难建立相等关系。在Postgres 9.4或更高版本中，可以考虑使用jsonb，在这种情况下是可能的。有关更多详细信息，请参阅dba.SE上的相关答案（最后一章）：

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

将表达式的两侧都转换为text，可以使用=或<>运算符，但通常不可靠，因为同一个JSON值有许多可能的文本表示。然而，在这个特定情况（空对象）中，它完全正常：

SELECT * FROM test WHERE foo::text <> '{}'::text;

在Postgres 9.4或更高版本中，转换为jsonb。(或者一开始就使用jsonb。)
SELECT * FROM test WHERE foo::jsonb <> '{}'::jsonb;

或者，为了绝对涵盖所有可能性：
SELECT * FROM test
WHERE  CASE json_typeof(foo)
          WHEN 'object' THEN foo::text <> '{}'::text
          WHEN 'array'  THEN foo::text <> '[]'::text
          ELSE true  -- other types are not are not "empty"
          END;

json_typeof()的手册：

可能的类型有object、array、string、number、boolean和null。

4
同样适用于空数组，只需将{}替换为[]即可。 - hobberwickey
3
如果您正在寻找嵌套结构，则可以使用以下语句：select * from test where foo->>'property' = '[]';其中的结构可能类似于：{ "property": [], "foo": "bar" }。请注意，该语句仅返回具有名为“property”的空数组属性的行。 - Dynom
这很糟糕，当有大量行时，每个 foo 都是一个大结构体；每个结构体都被强制转换为文本！ - EoghanM
当我使用它时，我不想添加'{} :: text'。可以吗？（我也没有使用foo :: text） - Kavinda Jayakody
18

空的JSON数组 [] 也可能是相关的。

那么这个可以同时应用于 [] 和 {}: 

select * from test where length(foo::text) > 2 ;
7

你需要小心。将所有数据强制转换为不同类型进行比较会在大型数据库上导致性能问题。

如果你的数据有一个一致的键，那么可以查找该键是否存在。例如，如果计划数据是{}或{id：'1'}，则可以查找没有'id'的项。

SELECT * FROM public."user"
where NOT(plan ? 'id')
6
3
在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 
-----
 {}

或者测试存在性，对于大对象可能更快。

select *
from test
where not exists (select 1 from json_each(foo) s);

无论格式如何，这两种技术都可以完美地工作。
在这些示例中，为什么在json_each调用后面要加s？它有什么作用？ - Stratus3D
@Stratus3D 这是子查询的强制别名，本例中为函数。 - Clodoaldo Neto
通过将 json_each 替换为 json_array_elements，也可以对数组执行此操作。 - Anentropic
1
根据JSON函数和运算符文档，您可以使用双箭头函数(->>)将JSON对象或数组字段作为文本进行提取。然后根据字符串进行相等性检查。

这对我有用：

SELECT jsonb_col from my_table
WHERE jsonb_col ->> 'key' = '{}';

如果它嵌套超过一层，请使用path函数（#>>）

SELECT jsonb_col from my_table
WHERE jsonb_col #>> '{key, nestedKey}' = '{}';

截至本文撰写时，当前支持的版本为:

支持的版本: 当前版本 (13) / 12 / 11 / 10 / 9.6

（注意：保留html标签）
1
你可以使用Postgres的<@（包含于）运算符，参见docs。
select '{"a":2, "b":"hello"}'::jsonb <@ '{}'::jsonb;  -- false
select '{}'::jsonb <@ '{}'::jsonb;                    -- true

select '[{"a":1}, {"a":2}, {"a":3}]'::jsonb <@ '[]'::jsonb;  -- false
select '[]'::jsonb <@ '[]'::jsonb;                           -- true
0

PostgreSQL 12中的JSON函数提供了jsonb_path_exists功能。

为了避免序列化大型jsonb对象，此功能可以正确地返回true，如果对象不为空：

select data from block where jsonb_path_exists(data, '$ ? (exists (@.*))');
0
SELECT * FROM Table_Name WHERE JSON_LENGTH(column_name) = 0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接