查询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'

null

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

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

Answer 1

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

而是使用JSONB“contains”运算符@>与表达式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数组，因此我们需要匹配搜索项中的结构，并将数组元素括在方括号中。搜索普通记录时，删除数组方括号。

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

在FROM子句的横向联接中使用函数JSON_ARRY_ELEMENTS()解除JSON数组，并测试其元素：

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

db<>在这里摆弄
_旧sqlfiddle

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

• 查询JSON列中数组的元素