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

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;

Leave a Comment