How to use comparison where operator for JSON

Question

I get this error when querying with a json column:

(psycopg2.ProgrammingError) operator does not exist: json = text

The column is defined as JSON with SQLAlchemy:

json_data = db.Column(db.JSON, nullable=False)

How do you compare with Postgres?

Answer 1

There is no equality (or inequality) operator for the data type json. If you need to test the value as a whole, you might cast to jsonb:

... WHERE json_data::jsonb = jsonb '{}';

Or cast to text for simple cases:

... WHERE json_data::text = '{}';

But there are many valid text representations for the same json value - which is the reason why Postgres does not implement equality / inequality operators for the type.

See:

• How to query a json column for empty objects?