Postgresql数组求和

Question

给定PostgreSQL数据库表中的一个数组列，其中包含以下内容：

{{765,4},{767,3},{569,5},{567,3},{725,5}}

我怎么能计算出每个子阵列的所有第二元素的总和，即4+3+5+3+5

Answer 1

您可以尝试使用UNNEST，它将数组扩展为一组行，并按行号进行筛选：

SELECT *, (
SELECT SUM(v) 
FROM UNNEST(array_column) WITH ORDINALITY a(v, n) 
WHERE n % 2 = 0
) FROM your_table;

Answer 2

我能够通过使用jsonb数组来解决这里提出的目标
jsonbArray
[{"an": 4, "qid": 765}, {"an": 3, "qid": 767}, {"an": 5, "qid": 569}, {"an": 3, "qid": 567}, {"an": 5, "qid": 725}]

实现目标的查询：

WITH answers as (
SELECT
(jsonbArray -> 'an')::int as an,
(jsonbArray -> 'qid')::int as qid
FROM (
SELECT jsonb_array_elements(jsonbArray) AS jsonbArray
FROM user where id = 1
) AS s
group by qid, an
)
select sum(an) as score from answers where qid in (765,725)
Result: 
score
9