How to query a json column for empty objects?

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.

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;

Empty JSON array [] could also be relevant.

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

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

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!

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

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

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