聚合来自 jsonb 记录数组的数值(包括 NULL 值)

Question

我正在使用postgres 9.5，我的数据是这样的整数：

id | v1 | v2 | v3
---+----+----+------
 1 | 10 | 3  | null     
 2 | 5  |null| 1
 3 |null| 2  | 7

我创建了一个这样的jsonb数组：

 [{"v1": 10, "v2": 3, "v3": null}]  

我想运行比较和聚合（IE 总和所有 v1）。

1）jsonb_array_elements是正确的操作还是有更简单的方法？

2）如果jsonb_array_elements是最好的方法，我如何转换为整数并生成空值，以便我可以运行比较/aggs？

见 DBFIDDLE

我已经查看了几个堆栈问题，这就是我所得到的：

SELECT id, x->'v1' AS v1
FROM   base,jsonb_array_elements(j) t(x);

Answer 1

你快到了。

SELECT sum((x->>'v1')::int) AS v1_sum
FROM   base, jsonb_array_elements(j) t(x);

使用 ->> 运算符而不是 -> 获取text值;然后强制转换。将jsonb NULL 强制转换为 integer 失败，您希望从text开始。

如果有更多的值列，-运算符也可能变得有吸引力：从整行中形成一条jsonb记录并减去（删除）id键：

SELECT id, json_agg(j1) AS j
FROM  (SELECT id, to_jsonb(t) - 'id' AS j1 FROM t) t1
GROUP  BY id;

db<>在这里小提琴