查询JSON类型中的数组元素

Question

我正在尝试测试jsonPostgreSQL 9.3中的类型。我在一个json名为data的表中调用了一列reports。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如何使用新的PostgreSQL JSON数据类型中的字段进行查询？http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/我也尝试过这样的事情，但无济于事：SELECT json_array_elements(data->'objects') AS data from reportsWHERE  data->>'src' = 'foo.png';我不是SQL专家，所以我不知道我做错了什么。

Answer 1

json 在Postgres 9.3+

使用子句json_array_elements()中的横向连接中的函数来对JSON数组进行Unnest FROM并测试其元素：

WITH reports(data) AS (

   VALUES ('{"objects":[{"src":"foo.png"}, {"src":"bar.png"}]

           , "background":"background.png"}'::json)

   ) 

SELECT *

FROM   reports r, json_array_elements(r.data#>'{objects}') obj

WHERE  obj->>'src' = 'foo.png';

该CTE（WITH查询）只是替代了一张桌子reports。

或者，相当于只是一个单一的嵌套层次：

SELECT *

FROM   reports r, json_array_elements(r.data->'objects') obj

WHERE  obj->>'src' = 'foo.png';

->>，->和#>运营商的说明书中介绍。

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

SQL小提琴。

密切相关的答案：

在JSON列中查询数组元素

jsonb 在Postgres 9.4+

使用等效的jsonb_array_elements()。

更好的是，使用新的“包含”运算符@>（最好结合表达式上匹配的GIN索引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 数组，因此我们需要匹配搜索项中的结构并将数组元素包装到方括号中。搜索普通记录时删除数组括号。

详细说明和更多选项：

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