How to Query Elements Within JSON Arrays in PostgreSQL?

Query elements in JSON array in PostgreSQL

Introduction

SQL can effectively handle the storage of JSON data. This article will focus on how to query arrays stored in JSON objects in PostgreSQL.

Query JSON nested array

Suppose a JSON column named "data" contains an array of objects. The task is to retrieve all records matching a specific "src" value from the "objects" array.

Query using Lateral Join and Unnesting

Postgres provides the json_array_elements() function to expand JSON arrays. By combining it with lateral join you can get the desired result:

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

This query creates a lateral join for each JSON array element, allowing column obj to represent each object in the array.

Alternatives to PostgreSQL 9.4

For PostgreSQL 9.4 and later, you can take advantage of improved JSON processing:

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

In this query, the "@>" operator checks whether the array contains the specified object. Remember to enclose the search terms in square brackets to match the JSON array structure.

Summary

To query an array in a JSON type, you can use json_array_elements() to expand the array and perform a lateral join. For PostgreSQL 9.4 and later, you can use the enhanced "@>" operator along with matching GIN indexes for efficient matching. Both methods allow you to retrieve specific records based on array element values.

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