如何在 PostgreSQL 中查询嵌套 JSON 数组？

PostgreSQL中嵌套JSON数组的查询

简介：

PostgreSQL的JSON数据类型提供了全面的数据存储和查询功能。一个常见的挑战是查询存储在JSON对象中的数组。本文提供了一个逐步指导，用于查询JSON类型中的数组元素，重点关注PostgreSQL 9.3及以上版本。

在PostgreSQL 9.3及以上版本中查询JSON数组：

1. json_array_elements() 函数：

   json_array_elements() 函数返回JSON数组中所有元素的横向列表。它可以与FROM子句中的横向连接一起使用：

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

   请注意，#> 运算符用于访问JSON数组中的嵌套对象。

2. 嵌套横向连接：

   另一种方法是使用嵌套横向连接：

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

   此查询使用隐式JOIN LATERAL，其操作方式与前面的示例类似。

使用GIN索引优化查询（PostgreSQL 9.4 ）：

为了提高搜索特定数组元素时的查询性能，建议使用jsonb_array_elements()并在JSON表达式data->'objects'上创建GIN索引：

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

@> 运算符检查包含关系，并要求JSON结构用方括号括起来。

附加说明：

• ->, ->> 和 #> 运算符用于访问嵌套的JSON对象和数组。
• 有关JSON运算符的全面了解，请参阅PostgreSQL文档。

以上是如何在 PostgreSQL 中查询嵌套 JSON 数组？的详细内容。更多信息请关注PHP中文网其他相关文章！