How to query a json column for empty objects?

by

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;

More Related Contents:

  1. Flatten aggregated key/value pairs from a JSONB field?
  2. Explanation of JSONB introduced by PostgreSQL
  3. Merging JSONB values in PostgreSQL?
  4. In postgresql, how can I return a boolean value instead of string on a jsonb key?
  5. How do I modify fields inside the new PostgreSQL JSON datatype?
  6. Index for finding an element in a JSON array
  7. Query for array elements inside JSON type
  8. Postgres: How to convert a json string to text?
  9. PostgreSQL return result set as JSON array?
  10. Using jsonb_set() for updating specific jsonb array value
  11. How to create index on JSON field in Postgres?
  12. How to convert postgres json to integer
  13. Postgres JSON data type Rails query
  14. Check if a Postgres JSON array contains a string
  15. Collect Recursive JSON Keys In Postgres
  16. Size limit of JSON data type in PostgreSQL
  17. Operator does not exist: json = json
  18. Go JSON Naming strategy about extends
  19. How to parse/deserialize dynamic JSON
  20. How to use definitions in JSON schema (draft-04)
  21. How to format a JSON string as a table using jq?
  22. Why does RestTemplate not bind response representation to PagedResources?
  23. How to create dynamic routes with react-router-dom?
  24. Parsing multiple JSON objects in Go
  25. Is it possible to partially decode and update JSON? (go)
  26. Decoding Error — Expected to decode Dictionary but found an array instead
  27. Can I encrypt my JSON data?
  28. Call json.Unmarshal inside UnmarshalJSON function without causing stack overflow
  29. PostgreSQL jsonb, `?` and JDBC
  30. How to use Postgres JSONB datatype with JPA?

Leave a Comment