logo标签列表

如何从PostgreSQL的jsonb数组中获取特定对象？

postgresqlpostgresql-9.4arrayjson
我有一个名为'user'的字段，它保存了一个大致如下的JSON数组： 
[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]
现在我想要一个像这样的查询： 
select count from tablename where id = "1"
我无法从PostgreSQL 9.4中的一个JSON对象数组中获取特定字段count。
2个回答

将您的值存储在规范化的模式中会更加高效。话虽如此，您也可以使用当前的设置使其正常工作。

假设

假设表定义如下：

CREATE TABLE tbl (tbl_id int, usr jsonb);
"用户"是一个保留字，如果作为列名使用，需要加上双引号。但请不要这样做。我会使用usr代替。

查询

查询并不像（现已删除的）评论所说那么简单： 

SELECT t.tbl_id, obj.val->>'count' AS count
FROM   tbl t
JOIN   LATERAL jsonb_array_elements(t.usr) obj(val) ON obj.val->>'_id' = '1'
WHERE  t.usr @> '[{"_id":"1"}]';
有3个基本步骤：

1. 便宜地识别符合条件的行

WHERE t.usr @> '[{"_id":"1"}]' 用于识别JSON数组中具有匹配对象的行。该表达式可以使用在jsonb列上的通用GIN索引，或者使用更专门的操作符类jsonb_path_ops的索引：

CREATE INDEX tbl_usr_gin_idx ON tbl USING gin (usr jsonb_path_ops);
添加的WHERE子句在逻辑上是多余的，但是为了使用索引是必需的。连接子句中的表达式在每一行合格之后才对数组进行解嵌套，并强制执行相同的条件。有了索引支持，Postgres只处理包含合格对象的行。对于小表没有太大影响，但对于大表和只有少数合格行的情况则有很大差别。 相关链接： - PostgreSQL操作符使用索引，但底层函数不使用 - 在JSON数组中查找元素的索引

2. 在数组中识别匹配的对象

使用jsonb_array_elements()进行解嵌套。（unnest()仅适用于Postgres数组类型。）由于我们只对实际匹配的对象感兴趣，所以在连接条件中立即过滤。 相关内容：

3. 提取嵌套键'count'的值

在提取合格的对象之后，简单地使用：obj.val->>'count'。

从Postgres 12开始，您可以使用jsonb_path_query。

WITH cte (
    js
) AS (
    VALUES ('[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]'))
SELECT
    jsonb_path_query(js::jsonb, '$[*] ? (@._id == "1")') ->> 'count'
FROM
    cte;