查询JSON类型内的数组元素

我试图在PostgreSQL 9.3测试json类型。我有一个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'

但是由于“对象”有一个值数组，我似乎不能写一些有用的东西。是否有可能查询数据库中匹配'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专家，所以我不知道我做错了什么。

当前回答

创建一个列类型为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';

2018-08-30 14:04:14

其他回答

垃圾海报9.4+

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

而是使用jsonb "contains"操作符@>，并在表达式数据->'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"}]';

由于键对象包含一个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';

db < >小提琴老sqlfiddle

或者，相当于一个嵌套级别:

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

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

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

密切相关:

查询JSON列中的数组元素

2014-03-29 22:25:51