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

kxeu7u2r 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(146)

我目前正在编写一个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'

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

的数据
下面是先前结果的info列中的数据示例。可以看出，先前结果对于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"}]
}

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

postgresql

来源：https://stackoverflow.com/questions/77381676/extract-values-from-multiple-nested-arrays-in-a-jsonb-column

2条答案

按热度按时间

i2loujxw1#

避免这种行相乘的一种方法是将每个生成的集合聚合到一个数组中。比如：

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转换为 “sum all columns' values”。所以这个替代方法对每个数组中的值求和：

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   ...

型

赞(0）回复(0）举报 2023-11-18

r1zk6ea12#

如果你只想用NULL值填充，你可以把标量项 Package 成一个虚拟数组，然后像你做“自然”数组一样将它解嵌套。所以第4行变成：

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

字符串

赞(0）回复(0）举报 2023-11-18

我来回答

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

2条答案

相关问题

热门标签

最新问答