查询 JSON 类型内的数组元素 [英] Query for array elements inside JSON type

问题描述

我正在尝试测试 PostgreSQL 9.3 中的 json 类型.
我在名为 reports 的表中有一个名为 data 的 json 列.JSON 看起来像这样:

I'm trying to test out the json type in PostgreSQL 9.3.
I have a json column called data in a table called reports. The JSON looks something like this:

{
  "objects": [
    {"src":"foo.png"},
    {"src":"bar.png"}
  ],
  "background":"background.png"
}

我想在表中查询与objects"数组中的src"值匹配的所有报告.例如，是否可以在数据库中查询与 'src' = 'foo.png' 匹配的所有报告?我成功写了一个可以匹配"background"的查询:

I would like to query the table for all reports that match the 'src' value in the 'objects' array. For example, is it possible to query the DB for all reports that match 'src' = 'foo.png'? I successfully wrote a query that can match the "background":

SELECT data AS data FROM reports where data->>'background' = 'background.png'

但是由于 "objects" 有一个值数组，我似乎无法编写一些有效的东西.是否可以在数据库中查询与 'src' = 'foo.png' 匹配的所有报告?我已经查看了这些来源，但仍然无法获得:

But since "objects" has an array of values, I can't seem to write something that works. Is it possible to query the DB for all reports that match 'src' = 'foo.png'? I've looked through these sources but still can't get it:

• http://www.postgresql.org/docs/9.3/静态/函数-json.html
• 我如何查询使用新 PostgreSQL JSON 数据类型中的字段?
• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

我也试过这样的事情，但无济于事:

I've also tried things like this but to no avail:

SELECT json_array_elements(data->'objects') AS data from reports
WHERE  data->>'src' = 'foo.png';

我不是 SQL 专家，所以我不知道我做错了什么.

I'm not an SQL expert, so I don't know what I am doing wrong.

推荐答案

jsonb in Postgres 9.4+

您可以使用与下面相同的查询，只需使用 jsonb_array_elements().

jsonb in Postgres 9.4+

You can use the same query as below, just with jsonb_array_elements().

而是使用 jsonb contains"运算符 @> 与表达式 data->'objects' 上匹配的 GIN 索引组合:

But rather use the jsonb "contains" operator @> in combination with a matching GIN index on the expression data->'objects':

CREATE INDEX reports_data_gin_idx ON reports
USING gin ((data->'objects') jsonb_path_ops);

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

由于键 objects 包含一个 JSON 数组，我们需要匹配搜索词中的结构并将数组元素也包裹在方括号中.搜索普通记录时删除数组括号.

Since the key objects holds a JSON array, we need to match the structure in the search term and wrap the array element into square brackets, too. Drop the array brackets when searching a plain record.

更多解释和选项:

• 用于在元素中查找元素的索引JSON 数组

使用函数 取消嵌套 JSON 数组json_array_elements() 在 FROM 子句的横向连接中并测试其元素:

Unnest the JSON array with the function json_array_elements() in a lateral join in the FROM clause and test for its elements:

SELECT data::text, obj
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';

db<>fiddle 这里
_旧sqlfiddle

或者，相当于单级嵌套:

SELECT *
FROM   reports r, json_array_elements(r.data->'objects') obj
WHERE  obj->>'src' = 'foo.png';

->>、-> 和 #> 操作符在手册中有解释.

->>, -> and #> operators are explained in the manual.

两个查询都使用隐式JOIN LATERAL.

密切相关:

• 查询 JSON 列中的数组元素

这篇关于查询 JSON 类型内的数组元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！