How to query a json column for empty objects?
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 ;