How to Query Nested JSON Arrays in PostgreSQL?

Query for nested JSON arrays in PostgreSQL

Introduction:

PostgreSQL’s JSON data type provides comprehensive data storage and query functions. A common challenge is querying arrays stored in JSON objects. This article provides a step-by-step guide for querying array elements in JSON types, focusing on PostgreSQL 9.3 and above.

Querying JSON arrays in PostgreSQL 9.3 and above:

1. json_array_elements() function:

   json_array_elements() The function returns a horizontal list of all elements in the JSON array. It can be used with lateral joins in the FROM clause:

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

   Please note that the #> operator is used to access nested objects in a JSON array.

2. Nested horizontal joins:

   Another approach is to use nested lateral joins:

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

   This query uses implicit JOIN LATERAL and operates similarly to the previous example.

Optimize queries using GIN index (PostgreSQL 9.4):

To improve query performance when searching for specific array elements, it is recommended to use jsonb_array_elements() and create a GIN index on the JSON expression data->'objects':

<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>

The

@> operator checks for inclusion and requires the JSON structure to be enclosed in square brackets.

Additional notes:

The
• ->, ->> and #> operators are used to access nested JSON objects and arrays.
• For a comprehensive understanding of JSON operators, see the PostgreSQL documentation.

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