查询JSON类型内的数组元素。

12 浏览
0 Comments

查询JSON类型内的数组元素。

我正在尝试在PostgreSQL 9.3中测试json类型。
\n我在名为reports的表中有一个名为data的json列。JSON的结构如下：\n{\n \"objects\": [\n {\"src\":\"foo.png\"},\n {\"src\":\"bar.png\"}\n ],\n \"background\":\"background.png\"\n}\n\n我想要查询所有与\'objects\'数组中的\'src\'值匹配的报告。例如，是否可能查询与\'src\' = \'foo.png\'匹配的所有报告？我已经成功编写了一个可以匹配\"background\"的查询：\n

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

\n但是由于\"objects\"包含一个值数组，我无法编写出有效的查询语句。是否可能查询与\'src\' = \'foo.png\'匹配的所有报告？我已经查阅了这些资源，但仍然无法解决问题：\n

\n我也尝试过类似下面的语句，但均未成功：\n

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

\n我不是一个SQL专家，所以不知道我哪里出错了。

0

2 答案

0 Comments

在这个问题中，出现的原因是需要从JSON类型的数组中查询元素。解决方法是使用PostgreSQL的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中的数组。

如果想要检索数组元素的数量，可以使用`jsonb_array_length`函数：

select jsonb_array_length(data->'work') from friends;

以上就是如何在JSON类型的数组中进行查询的解决方法。

0
0 Comments

问题：在JSON类型的数组中查询数组元素的方法。

原因：在Postgres 9.3+中，可以使用函数`json_array_elements()`将JSON数组展开，并在查询中使用`FROM`子句的lateral join来测试元素。在Postgres 9.4+中，可以使用函数`jsonb_array_elements()`来实现相同的查询。但是，使用`jsonb`的"contains"运算符`@>`结合表达式`data->'objects'`上的匹配GIN索引更好。

解决方法：在Postgres 9.4+中，可以通过创建索引和使用`@>`运算符来查询JSON数组中的元素。在Postgres 9.3+中，可以使用`json_array_elements()`函数和lateral join来展开JSON数组，并测试元素。对于只有一个嵌套级别的情况，也可以使用`->`运算符来查询。

0