Query for array elements inside JSON type

by

jsonb in Postgres 9.4+

You can use the same query as below, just with jsonb_array_elements().

But rather use the jsonb “contains” operator @> in combination with a matching GIN index on the expression data->'objects':

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

SELECT * FROM reports WHERE data->'objects' @> '[{"src":"foo.png"}]';

Since the key objects holds a JSON array, we need to match the structure in the search term and wrap the array element into square brackets, too. Drop the array brackets when searching a plain record.

More explanation and options:

json in Postgres 9.3+

Unnest the JSON array with the function json_array_elements() in a lateral join in the FROM clause and test for its elements:

SELECT data::text, obj
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

db<>fiddle here
Old sqlfiddle

Or, equivalent for just a single level of nesting:

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->>, -> and #> operators are explained in the manual.

Both queries use an implicit JOIN LATERAL.

Closely related:

More Related Contents:

  1. Index for finding an element in a JSON array
  2. Query for element of array in JSON column
  3. Operator does not exist: json = json
  4. How do I query using fields inside the new PostgreSQL JSON datatype?
  5. How can I import a JSON file into PostgreSQL?
  6. Return multiple columns of the same row as JSON array of objects
  7. What’s the proper index for querying structures in arrays in Postgres jsonb?
  8. Postgres analogue to CROSS APPLY in SQL Server
  9. How to convert PostgreSQL 9.4’s jsonb type to float
  10. PostgreSQL Index on JSON
  11. Sort by column ASC, but NULL values first?
  12. Using an Alias column in the where clause in Postgresql
  13. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  14. Pivot on Multiple Columns using Tablefunc
  15. Update multiple rows in same query using PostgreSQL
  16. Humanized or natural number sorting of mixed word-and-number strings
  17. postgres column “X” does not exist
  18. Multiple CTE in single query
  19. Return pre-UPDATE column values using SQL only
  20. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  21. Selecting data into a Postgres array
  22. SQL syntax term for ‘WHERE (col1, col2) < (val1, val2)'
  23. PostgreSQL: encoding problems on Windows when using psql command line utility
  24. Get the distinct sum of a joined table column
  25. Aggregate a single column in query with many columns
  26. Aggregating (x,y) coordinate point clouds in PostgreSQL
  27. Does Postgres support nested or autonomous transactions?
  28. Postgres Error: More than one row returned by a subquery used as an expression
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment