sql - 查询 JSON 类型内的数组元素

Question

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

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

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

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

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

• http://www.postgresql.org/docs/9.3/static/functions-json.html
• How do I query using fields inside the new PostgreSQL JSON datatype?
• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/

我也尝试过类似的方法，但无济于事:

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

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

Answer 1

jsonb 在 Postgres 9.4+

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

而是将 jsonb “包含”运算符 @> 与表达式 data->'objects' 上的匹配 GIN 索引结合使用>:

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 array，因此我们需要匹配搜索词中的结构并将数组元素也包含在方括号中。搜索普通记录时删除数组括号。

更多解释和选项:

• Index for finding an element in a JSON array

json 在 Postgres 9.3+

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

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

db<> fiddle here
_{旧 sqlfiddle}

或者，仅相当于单层的嵌套:

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

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

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

密切相关:

• Query for element of array in JSON column