代码之家 › 专栏 › 技术社区 › 0x55b1E06FF

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

jsonb subquery postgresql database sql

0x55b1E06FF · 技术社区 · 2 年前

我目前正在研究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"}]
}

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

2 回复 | 直到 2 年前

Erwin Brandstetter 2 年前

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

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或更高版本中,最大数组中的行数与元素数一样多。请参阅:

What is the expected behaviour for multiple set-returning functions in SELECT clause?

你需要选到 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   ...

jjanes 2 年前

如果您只想用NULL值填充,可以将标量条目封装到一个伪数组中,然后像执行“自然”数组一样取消测试。因此第4行变为:

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