SQL 查询 JSON 类型内的数组元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22736742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query for array elements inside JSON type
提问by pacothelovetaco
I'm trying to test out the jsontype in PostgreSQL 9.3.
I have a jsoncolumn called datain a table called reports. The JSON looks something like this:
我正在尝试测试jsonPostgreSQL 9.3 中的类型。
我有一个json叫列data在一个名为表reports。JSON 看起来像这样：
{
"objects": [
{"src":"foo.png"},
{"src":"bar.png"}
],
"background":"background.png"
}
I would like to query the table for all reports that match the 'src' value in the 'objects' array. For example, is it possible to query the DB for all reports that match 'src' = 'foo.png'? I successfully wrote a query that can match the "background":
我想在表中查询与 'objects' 数组中的 'src' 值匹配的所有报告。例如，是否可以在数据库中查询所有匹配的报告'src' = 'foo.png'？我成功地编写了一个可以匹配的查询"background"：
SELECT data AS data FROM reports where data->>'background' = 'background.png'
But since "objects"has an array of values, I can't seem to write something that works. Is it possible to query the DB for all reports that match 'src' = 'foo.png'? I've looked through these sources but still can't get it:
但是由于"objects"有一组值，我似乎无法写出有效的东西。是否可以在数据库中查询所有匹配的报告'src' = 'foo.png'？我已经查看了这些来源，但仍然无法获得：
- http://www.postgresql.org/docs/9.3/static/functions-json.html
- How do I query using fields inside the new PostgreSQL JSON datatype?
- http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-json-operators/
- 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/
I've also tried things like this but to no avail:
我也试过这样的事情，但无济于事：
SELECT json_array_elements(data->'objects') AS data from reports
WHERE data->>'src' = 'foo.png';
I'm not an SQL expert, so I don't know what I am doing wrong.
我不是 SQL 专家，所以我不知道我做错了什么。
回答by Erwin Brandstetter
jsonin Postgres 9.3+
json在 Postgres 9.3+
Unnest the JSON array with the function json_array_elements()in a lateral join in the FROMclause and test for its elements:
使用子句中json_array_elements()横向连接中的函数取消嵌套 JSON 数组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';The CTE(WITHquery) just substitutes for a table reports.
Or, equivalent for just a singlelevel of nesting:
该CTE（WITH查询）只是替代了一张桌子reports。
或者，相当于只是一个单一的嵌套层次：
SELECT *
FROM reports r, json_array_elements(r.data->'objects') obj
WHERE obj->>'src' = 'foo.png';->>, ->and #>operators are explained in the manual.
Both queries use an implicit JOIN LATERAL.
两个查询都使用隐式JOIN LATERAL.
Closely related answer:
密切相关的答案：
jsonbin Postgres 9.4+
jsonb在 Postgres 9.4+
Use the equivalent jsonb_array_elements().
使用等效的jsonb_array_elements().
Betteryet, use the new "contains" operator @>(best in combination with a matching GIN index on the expression data->'objects'):
更好的是，使用新的“包含”运算符@>（最好与表达式上的匹配 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"}]';
Since the key objectsholds a JSON array, we need to match the structure in the search term and wrap the array element into square brackets, too. Drop the array brackets when searching a plain record.
由于键objects包含一个 JSON数组，我们需要匹配搜索词中的结构并将数组元素也包裹在方括号中。搜索普通记录时删除数组括号。
Detailed explanation and more options:
详细说明和更多选项：
回答by Sandip Debnath
Create a table with column as type json
创建一个列为 json 类型的表
CREATE TABLE friends ( id serial primary key, data jsonb);
Now let's insert json data
现在让我们插入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}');
Now let's make some queries to fetch data
现在让我们做一些查询来获取数据
select data->'name' from friends;
select data->'name' as name, data->'work' as work from friends;
You might have noticed that the results comes with inverted comma( " ) and brackets ([ ])
您可能已经注意到结果带有引号 (") 和括号 ([])
name | work
------------+----------------------------
"Arya" | ["Improvements", "Office"]
"Tim Cook" | ["Cook", "ceo", "Play"]
(2 rows)
Now to retrieve only the values just use ->>
现在只检索值，只需使用 ->>
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';
回答by anand shukla
select data->'objects'->0->'src' as SRC from table where data->'objects'->0->'src' = 'foo.png'
select data->'objects'->0->'src' as SRC from table where data->'objects'->0->'src' = 'foo.png'
