Query for element of array in JSON column

by

For Postgres 9.4+ see adamc’s later answer. Or:

Original answer for Postgres 9.3

Yes, that’s possible:

SELECT *
FROM   tbl t, json_array_elements(t.json_col->'emails') AS elem
WHERE  elem->>'id' = 123;

tbl being your table name, json_col the name of the JSON column.

See also:

About the implicit CROSS JOIN LATERAL:

Index to support this kind of query:

More Related Contents:

  1. How do I query using fields inside the new PostgreSQL JSON datatype?
  2. Query for array elements inside JSON type
  3. Index for finding an element in a JSON array
  4. How can I import a JSON file into PostgreSQL?
  5. Return multiple columns of the same row as JSON array of objects
  6. Split given string and prepare case statement
  7. Postgres analogue to CROSS APPLY in SQL Server
  8. PostgreSQL Index on JSON
  9. Parallel unnest() and sort order in PostgreSQL
  10. PostgreSQL 9.3: Dynamic pivot table
  11. Operator does not exist: json = json
  12. Select rows which are not present in other table
  13. Optimize GROUP BY query to retrieve latest row per user
  14. How to implement a many-to-many relationship in PostgreSQL?
  15. Check if value exists in Postgres array
  16. How to use ANY instead of IN in a WHERE clause?
  17. Is there a shortcut for SELECT * FROM?
  18. sql statement error: “column .. does not exist”
  19. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  20. sql ORDER BY multiple values in specific order?
  21. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  22. Sorting null values after all others, except special
  23. Reference alias in WHERE clause
  24. PostgreSQL ORDER BY issue – natural sort
  25. Discard millisecond part from timestamp
  26. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  27. SQL column reference “id” is ambiguous
  28. How to use multiple CTEs in a single SQL query?
  29. IN Clause with NULL or IS NULL
  30. Temporal database design, with a twist (live vs draft rows)

Leave a Comment