postgresql 如何在json列中查询空对象?

twh00eeo  于 2023-04-20  发布在  PostgreSQL
关注(0)|答案(7)|浏览(188)

查找某个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.
gev0vcfq

gev0vcfq1#

对于数据类型**json**,没有相等(或不等)运算符,因为相等很难建立。考虑在Postgres 9.4或更高版本中的jsonb,这是可能的。在dba.SE上的相关答案(最后一章)中有更多细节:

SELECT DISTINCT json_column ...... GROUP BY json_column失败的原因相同(没有相等运算符)。
将表达式的两端转换为text允许=<>运算符,但这通常不可靠,因为对于 * 相同 * 的JSON值有许多可能的文本表示。在Postgres 9.4或更高版本中,转换为jsonb。(或使用jsonb开始。)

然而,对于这种特殊情况空对象),它工作得很好:

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

zed5wv102#

空JSON数组[]也可能是相关的。
然后这可以同时适用于[]{}

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

b4lqfgs43#

你必须要小心。将所有数据转换为不同的类型以便比较,这在大型数据库上会产生性能问题。
如果您的数据具有一致的键,则可以查找键的存在。例如,如果计划数据为{}或{id:'1'}
然后你可以查找没有'id'的项目

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

jmp7cifd4#

从PostgreSQL 9.5开始,这种类型的JSON数据查询是不可能的。另一方面,我同意这将是非常有用的,并为此创建了一个请求:
https://postgresql.uservoice.com/forums/21853-general/suggestions/12305481-check-if-json-is-empty
请随意投票,希望它能得到实施!

o7jaxewo

o7jaxewo5#

在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);

这两种技术都可以完美地工作,而不管格式如何

qnakjoqk

qnakjoqk6#

根据JSON Functions and Operators documentation,你可以使用双箭头函数(->>)来获取一个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

dly7yett

dly7yett7#

JSON Functions in PostgreSQL 12具有jsonb_path_exists
为了避免序列化大的jsonb对象,如果object不为空,则正确返回true:

select data from block where jsonb_path_exists(data, '$ ? (exists (@.*))');

相关问题