如何在 PostgreSQL 中查询 JSON 数组中的元素？

在PostgreSQL中查询JSON数组中的元素

简介

SQL能够有效地处理JSON数据的存储。本文将重点介绍如何在PostgreSQL中查询存储在JSON对象中的数组。

查询JSON嵌套数组

假设一个名为“data”的JSON列包含一个对象数组。任务是从“objects”数组中检索所有与特定“src”值匹配的记录。

使用Lateral Join和Unnesting进行查询

Postgres提供了json_array_elements()函数来展开JSON数组。通过将其与lateral join结合使用，可以获得所需的结果：

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

此查询为每个JSON数组元素创建一个lateral join，允许obj列表示数组中的每个对象。

PostgreSQL 9.4 的替代方法

对于PostgreSQL 9.4及更高版本，您可以利用改进的JSON处理功能：

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

在此查询中，“@>”运算符检查数组是否包含指定的对象。请记住将搜索词括在方括号中以匹配JSON数组结构。

总结

要查询JSON类型中的数组，您可以使用json_array_elements()展开数组并执行lateral join。对于PostgreSQL 9.4及更高版本，您可以使用增强的“@>”运算符以及匹配的GIN索引来进行高效匹配。这两种方法都允许您根据数组元素值检索特定记录。

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