从 JSONB 列中的多个嵌套数组中提取值-解网

问：

我目前正在处理一个PostgreSQL查询，以从JSONB列中提取特定值。这是我正在使用的查询：

select
a.id,
(jsonb_array_elements(a.info->'attribute1')->>'value') as attribute1,
(a.info->>'attribute2') as attribute2,
(a.info->>'attribute3') as attribute3,
(jsonb_array_elements(a.info->'attribute4')->>'value') as attribute4
from a_table a
where
(cast(a.info->>'attribute3' as NUMERIC) > 0
or jsonb_array_length(a.info->'attribute1') > 0
or jsonb_array_length(a.info->'attribute4') > 0
or cast(a.info->>'attribute2' as NUMERIC) > 0)
and a.active=true
and a.data='AAA0000'

我面临的问题是，它复制的次数与（或具有更多寄存器的任何其他属性）一样多，当我使用此查询作为子查询来对所有列的值求和时，会产生不正确的结果。attribute3attribute1

此查询的结果如下：

下面是上一个结果的信息列中的数据示例。可以看出，前面的结果对于 attribute3 是不正确的。

{
"attribute1": [{"value": 30.45, "description": "abc1"}, {"value": 5, "description": "abc2"}, {"value": 5, "description": "abc3"}], 
"attribute2": 0, 
"attribute3": 69.36, 
"attribute4": [{"value": 18, "description": "aaa"}]
}

我正在寻找一种方法来修改查询以防止复制属性值。

SQL 数据库 PostgreSQL 子查询 jsonb

SELECT a.id
     , ARRAY(SELECT (jsonb_array_elements(a.info -> 'attribute1') ->> 'value')::numeric) AS attribute1
     , (a.info ->> 'attribute2')::numeric AS attribute2
     , (a.info ->> 'attribute3')::numeric AS attribute3
     , ARRAY(SELECT (jsonb_array_elements(a.info -> 'attribute4') ->> 'value')::numeric) AS attribute4
FROM ...

否则，您将获得与最大数组包含元素一样多的行 - 在 Postgres 10 或更高版本中。看：

SELECT 子句中多个集合返回函数的预期行为是什么？

您需要强制转换为“将所有列的值相加”。因此，此替代方法汇总了每个数组中的值：numeric

SELECT a.id
     , (SELECT sum((a1.elem ->> 'value')::numeric) FROM jsonb_array_elements(a.info -> 'attribute1') a1(elem)) AS attribute1
     , (a.info ->> 'attribute2')::numeric AS attribute2
     , (a.info ->> 'attribute3')::numeric AS attribute3
     , (SELECT sum((a4.elem ->> 'value')::numeric) FROM jsonb_array_elements(a.info -> 'attribute4') a4(elem)) AS attribute4
FROM   ...

1赞 jjanes 10/29/2023 #2

如果只想填充 NULL 值，则可以将标量条目包装到虚拟数组中，然后像执行“自然”数组一样取消嵌套。所以第 4 行变成：

jsonb_array_elements(jsonb_build_array(a.info->'attribute2')) as attribute2,

上一个：Hive SQL：运行查询，如果第一个子查询返回 0 个结果，它将返回第二个子查询的结果

下一个：用于计算 jsonb 数据类型列中值出现的 PostgreSQL 查询

从 JSONB 列中的多个嵌套数组中提取值

Extract values from multiple nested arrays in a JSONB column

评论