查询JSON类型中的数组元素

Question

我在试着测试json键入PostgreSQL 9.3。

我有一个json列被称为data在一个名为的表中reports。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 datatype?中的字段进行查询
• 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专家，所以我不知道我做错了什么。

Answer 1

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