小编典典

查询JSON类型内的数组元素

json

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

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

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

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

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

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

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

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


阅读 281

收藏
2020-07-27

共1个答案

小编典典

json 在Postgres 9.3+中

json_array_elements()通过FROM子句中的横向联接将函数与JSON数组嵌套在一起,并测试其元素:

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';

CTEWITH查询)只是替代了一张桌子reports
或者,等效于 单层 嵌套:

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

->>->以及#>操作符在手册中进行了说明。

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

SQL提琴。

jsonb 在Postgres 9.4+

使用等效项jsonb_array_elements()

更好的 是,使用新的“ contains”运算符@>(最好与表达式上匹配的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 数组 ,因此我们需要匹配搜索项中的结构并将数组元素也包装在方括号中。搜索纯记录时,请放下数组括号。

2020-07-27