How do I query using fields inside the new PostgreSQL JSON datatype?

by

Postgres 9.2

I quote Andrew Dunstan on the pgsql-hackers list:

At some stage there will possibly be some json-processing (as opposed
to json-producing) functions, but not in 9.2.

Doesn’t prevent him from providing an example implementation in PLV8 that should solve your problem. (Link is dead now, see modern PLV8 instead.)

Postgres 9.3

Offers an arsenal of new functions and operators to add “json-processing”.

The answer to the original question in Postgres 9.3:

SELECT *
FROM   json_array_elements(
  '[{"name": "Toby", "occupation": "Software Engineer"},
    {"name": "Zaphod", "occupation": "Galactic President"} ]'
  ) AS elem
WHERE elem->>'name' = 'Toby';

Advanced example:

For bigger tables you may want to add an expression index to increase performance:

Postgres 9.4

Adds jsonb (b for “binary”, values are stored as native Postgres types) and yet more functionality for both types. In addition to expression indexes mentioned above, jsonb also supports GIN, btree and hash indexes, GIN being the most potent of these.

The manual goes as far as suggesting:

In general, most applications should prefer to store JSON data as
jsonb, unless there are quite specialized needs, such as legacy
assumptions about ordering of object keys.

Bold emphasis mine.

Performance benefits from general improvements to GIN indexes.

Postgres 9.5

Complete jsonb functions and operators. Add more functions to manipulate jsonb in place and for display.

More Related Contents:

  1. Query for element of array in JSON column
  2. Index for finding an element in a JSON array
  3. Query for array elements inside JSON type
  4. How can I import a JSON file into PostgreSQL?
  5. Return multiple columns of the same row as JSON array of objects
  6. What is the maximum number of columns in a PostgreSQL select query
  7. Split given string and prepare case statement
  8. NOT NULL constraint over a set of columns
  9. PostgreSQL Index on JSON
  10. Parallel unnest() and sort order in PostgreSQL
  11. PostgreSQL 9.3: Dynamic pivot table
  12. Operator does not exist: json = json
  13. Delete duplicate rows from small table
  14. Two SQL LEFT JOINS produce incorrect result
  15. Postgresql GROUP_CONCAT equivalent?
  16. Humanized or natural number sorting of mixed word-and-number strings
  17. Custom SERIAL / autoincrement per group of values
  18. postgres column “X” does not exist
  19. PostgreSQL: encoding problems on Windows when using psql command line utility
  20. Aggregate a single column in query with many columns
  21. Aggregating (x,y) coordinate point clouds in PostgreSQL
  22. Does Postgres support nested or autonomous transactions?
  23. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  24. Update a column of a table with a column of another table in PostgreSQL
  25. Query json dictionary data in SQL
  26. Check if NULL exists in Postgres array
  27. PostgreSQL how to create a copy of a database or schema?
  28. PostgreSQL: fill NULL values in timeserie query with previous value
  29. Postgres: convert single row to multiple rows (unpivot)
  30. Get most common value for each value of another column in SQL

Leave a Comment