I’m trying to test out the json
type in PostgreSQL 9.3.
I have a json
column called data
in a table called reports
. The JSON looks something like this:
{ "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" }
I would like to query the table for all reports that match the ‘src’ value in the ‘objects’ array. For example, is it possible to query the DB for all reports that match 'src' = 'foo.png'
? I successfully wrote a query that can match the "background"
:
SELECT data AS data FROM reports where data->>'background' = 'background.png'
But since "objects"
has an array of values, I can’t seem to write something that works. Is it possible to query the DB for all reports that match 'src' = 'foo.png'
? I’ve looked through these sources but still can’t get it:
- http://www.postgresql.org/docs/9.3/static/functions-json.html
- How do I query using fields inside the new PostgreSQL JSON datatype?
- http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/
I’ve also tried things like this but to no avail:
SELECT json_array_elements(data->'objects') AS data from reports WHERE data->>'src' = 'foo.png';
I’m not an SQL expert, so I don’t know what I am doing wrong.
Advertisement
Answer
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:
WITH reports(data) AS ( VALUES ('{"objects":[{"src":"foo.png"}, {"src":"bar.png"}] , "background":"background.png"}'::json) ) SELECT * FROM reports r, json_array_elements(r.data#>'{objects}') obj WHERE obj->>'src' = 'foo.png';
The CTE (WITH
query) just substitutes for a table reports
.
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 answer:
jsonb
in Postgres 9.4+
Use the equivalent jsonb_array_elements()
.
Better yet, use the new “contains” operator @>
(best 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.
Detailed explanation and more options: