PostgreSQL中对text列存储的JSON数组指定字段求和

• 表名：slow
• 字段：additional 类型为text，存储JSON格式数据，示例值：

{"default":[{"value_1": 100, "value_2": 0.1},{"value_1": 200, "value_2": 0.2}], "non_default":[{"value_1": 200, "value_2": 0.1}, {"value_1": 100, "value_2": 0.1}]}

• 目标：计算default键对应JSON数组内所有元素的value_1字段总和
• 原执行SQL返回null，错误语句：

select sum(cast(additional ::json-> 'default' ::text->> 'value_1' as integer)) as sum_default from "slow" where id = 'id'

1. 路径解析逻辑错误：additional::json -> 'default'取到的是JSON数组，数组的键为数字索引，不存在value_1这个属性名，直接用->>取值会返回null
2. 缺少数组展开步骤：没有将JSON数组拆分为单个元素，无法遍历访问每个元素内部的value_1字段

使用json_array_elements函数展开default对应的JSON数组，再逐元素提取value_1求和即可：

SELECT SUM((elem ->> 'value_1')::INTEGER) AS sum_default
FROM "slow",
     json_array_elements(additional::JSON -> 'default') AS elem
WHERE id = 'id';

如果使用jsonb类型操作（性能更优），写法如下：

SELECT SUM((elem ->> 'value_1')::INTEGER) AS sum_default
FROM "slow",
     jsonb_array_elements(additional::JSONB -> 'default') AS elem
WHERE id = 'id';

针对示例数据，上述语句执行后返回结果为300，符合预期。

内容的提问来源于stack exchange，提问作者wuku