查询 JSON 类型内的数组元素
我正在尝试测试 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 数据类型中的字段进行查询？
http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/
我也尝试过这样的事情，但无济于事：
SELECT json_array_elements(data->'objects') AS data from reports
WHERE data->>'src' = 'foo.png';
我不是 SQL 专家，所以我不知道我做错了什么。
Postgres 9.4+ 中的 jsonb
您可以使用与以下相同的查询，只需使用 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
在 FROM 子句的横向连接中使用函数 json_array_elements() 取消嵌套 JSON 数组并测试其元素：
SELECT data::text, obj
FROM reports r, json_array_elements(r.data#>'{objects}') obj
WHERE obj->>'src' = 'foo.png';或者，仅相当于一层嵌套：
SELECT *
FROM reports r, json_array_elements(r.data->'objects') obj
WHERE obj->>'src' = 'foo.png';
->>, -> and #> operators are explained in the manual.
两个查询都使用隐式 JOIN LATERAL。
密切相关：
查询 JSON 列中的数组元素
创建一个列类型为 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';
关注公众号
不定期副业成功案例分享
jsonb/ pg 9.4 的更新。另外：对于简单的情况（1 级嵌套），->运算符也可以在 pg 9.3 中为json解决问题。