如何查询空对象的 json 列？

查找某个 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.
98847 次浏览
小开
最佳答案

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):

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

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;
小开

In 9.3 it is possible to count the pairs in each object and filter the ones with none

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

小开

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:

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!

小开

Empty JSON array [] could also be relevant.

Then this could work for both [] and {}:

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

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'}

then you can look for items without 'id'

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

According to the JSON Functions and Operators documentation you can use the double arrow function (->>) to get a json object or array field as text. Then do an equality check against a string.

So this worked for me:

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

Or if it's nested more than one level use the path function (#>>)

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

Currently supported version as of this writing:

Supported Versions: Current (13) / 12 / 11 / 10 / 9.6


提交答案