Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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;
 ---------
  {"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.
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):
How to remove known elements from a JSON[] array in PostgreSQL?
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;
                If you're looking for nested structures, the following might be something you  could use: select * from test where foo->>'property' = '[]'; where the structure might be something like: { "property": [], "foo": "bar" }
– Dynom
                Dec 10, 2015 at 12:56
                This is pretty terrible where there are lots of rows, and each foo is a large structure; each one is coerced to text!
– EoghanM
                Apr 8, 2016 at 10:29
                When i'm using this i didn' want to add '{}'::text. Is it alright? (I didn't used the foo::text also)
– Kavinda Jayakody
                Aug 7, 2019 at 7:32
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')
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!
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;
-----
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
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
The JSON Functions in PostgreSQL 12 features jsonb_path_exists.
To avoid serializing big jsonb objects, this correctly returns true if object is not empty:
select data from block where jsonb_path_exists(data, '$ ? (exists (@.*))');
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.