查询JSON类型内的数组元素

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

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

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

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

但是由于"objects"有一个值数组，我似乎不能写一些有用的东西。是否有可能查询DB中所有匹配'src' = 'foo.png'的报告?我已经看了这些资料，但仍然找不到:

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

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

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

291670 次浏览
小开
最佳答案

jsonb in Postgres 9.4+

.

你可以使用与下面相同的查询，只是使用jsonb_array_elements()。

而是使用jsonb "contains"运算符@>结合表达式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 in Postgres 9.3+

.

在FROM子句的横向连接中使用函数json_array_elements()解嵌套JSON数组，并测试其元素:

SELECT data::text, obj
FROM   reports r, json_array_elements(r.data#>'{objects}') obj
WHERE  obj->>'src' = 'foo.png';
< p > Db<>fiddle here .
老sqlfiddle子> < /

或者，相当于单级别的嵌套:

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

->>， ->和#>操作符在手册中有解释

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

密切相关:

小开

创建一个列类型为json的表

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

现在让我们插入json data

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

提交答案