查询 JSON 类型内的数组元素

小编典典

查询 JSON 类型内的数组元素

all

我正在尝试测试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'？我已经查看了这些来源，但仍然无法得到它：

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

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

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

阅读 101

2022-07-28

共1个答案

小编典典

`jsonb`在 Postgres 9.4+

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

而是将jsonb“包含”运算符@>与表达式上的匹配 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"}]';

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

更多解释和选项：

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

`json`在 Postgres 9.3+

将 JSON
数组与子句json_array_elements()中的横向连接中的函数取消嵌套FROM并测试其元素：

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

db <>fiddle
here
旧sqlfiddle

或者，仅相当于一层嵌套：

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

->>,操作->符#>在手册中有说明。

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

2022-07-28