How to Query for JSON Array Element Matches in PostgreSQL?

Query JSON array element matching in PostgreSQL

PostgreSQL's JSON data type can be tricky when querying specific elements in nested arrays. Consider the following example:

<code>{
  "objects": [{"src":"foo.png"},
{"src":"bar.png"}],
  "background":"background.png"
}</code>

In order to query the records whose "src" value matches "foo.png" in the "objects" array, you can use a lateral join in the FROM clause.

PostgreSQL 9.3 and above

For PostgreSQL 9.3 and above, use the json_array_elements() function to expand the "objects" array and test each element:

<code class="language-sql">SELECT data::text, obj
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';</code>

PostgreSQL 9.4 and above

In PostgreSQL 9.4 and above, you should use the jsonb_array_elements() function and create a matching GIN index on the expression:

<code class="language-sql">CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

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

Remember that for array matching, the elements need to be enclosed in square brackets, while normal object matching does not require brackets. For more details and workarounds, see the linked documentation and other resources provided in the question.

The above is the detailed content of How to Query for JSON Array Element Matches in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!