查询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
• 如何使用 PostgreSQL 中新的 JSON 数据类型内部的字段进行查询？
• PostgreSQL 9.3 特性：JSON 操作符

我还尝试了类似以下代码，但均无效：

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

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

- pacothelovetaco

Answer 1

jsonb在Postgres 9.4+中

你可以使用与9.3+相同的查询，只需将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数组，我们需要在搜索条件中匹配结构并将数组元素也用方括号括起来。在搜索普通记录时，去掉数组括号。

更多解释和选项：

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

Postgres 9.3+中的json

使用函数json_array_elements()在FROM子句的侧面连接中展开JSON数组，并测试其元素。

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

db<>fiddle 这里
_{旧版 sqlfiddle}

或者，只有一个单独嵌套层级的等效方式：

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

->>、->和#>操作符在手册中有详细解释。

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

相关链接：

• 在JSON列中查询数组元素

- Erwin Brandstetter

---

@pacothelovetaco：为jsonb / pg 9.4添加了更新。另外：对于简单情况（1级嵌套），->运算符在pg 9.3中也适用于json。 - Erwin Brandstetter

@pacothelovetaco，对于pg 9.3版本，'#>'并不是秘密酱料，'->'对于你的情况也完全可以，因为它也返回json对象。'#>'在嵌套的json路径情况下更有帮助，因为它允许您轻松地在'{}'中指定路径。 - RoundPi

@> '[{"src":"foo.png"}]'；在where条件中工作正常，但如何删除特定的对象？我不知道这个对象的索引。我想通过键值进行删除。 - Pranay Soni

@PranaySoni：请用问题提出新问题。评论不是合适的地方。您始终可以链接到此处以获得上下文。 - Erwin Brandstetter

亲爱的@ErwinBrandstetter，是否可以通过部分匹配找到两个文档？例如，我想获取类似于'[{"src":".png"}]'的两个记录。 - Pyrejkee

Answer 2

创建一张列类型为JSON的表。

CREATE TABLE friends ( id serial primary key, data jsonb);

现在让我们插入JSON数据

INSERT INTO friends(data) VALUES ('{"name": "Arya", "work": ["Improvements", "Office"], "available": true}');
INSERT INTO friends(data) VALUES ('{"name": "Tim Cook", "work": ["Cook", "ceo", "Play"], "uses": ["baseball", "laptop"], "available": false}');

现在让我们进行一些查询以获取数据。

select data->'name' from friends;
select data->'name' as name, data->'work' as work from friends;

你可能已经注意到结果以列表形式呈现。

    name    |            work            
------------+----------------------------
 "Arya"     | ["Improvements", "Office"]
 "Tim Cook" | ["Cook", "ceo", "Play"]
(2 rows)

现在要仅检索值，只需使用->>

select data->>'name' as name, data->'work'->>0 as work from friends;
select data->>'name' as name, data->'work'->>0 as work from friends where data->>'name'='Arya';

- Sandip Debnath

---

我发现这很有用。它展示了如何在jsonb中钻取数组。 - GavinBelson

找到了很多复杂的方法，但这个非常简单易懂！ - Shashank Yadav

如何获取数组元素的数量？ - ZedZip