PostgreSQL中如何将jsonb嵌套字符串字段转换为int并求和

报错核心原因 #

你出现报错的核心原因是->操作符返回的结果是jsonb类型，无法直接作为数值函数的入参，也不能直接转换为数值类型，需要先转为数据库原生的文本类型再做后续转换。

两种json取值操作符的差异 #

• -> ：返回json/jsonb类型的结果，保留json类型属性
• ->> ：直接返回text类型的纯文本结果，适合后续做类型转换

正确写法 #

写法1（推荐）：用->>直接取文本后转数值求和 #

如果value都是整数，使用如下语句：

SELECT sum((amount->>'value')::INTEGER) FROM 你的表名;

如果value可能包含小数，转numeric/decimal即可：

SELECT sum((amount->>'value')::NUMERIC) FROM 你的表名;

写法2：使用->取值后先转文本再转数值 #

如果你要保留->的写法，需要多做一次文本类型转换：

SELECT sum((amount->'value')::TEXT::INTEGER) FROM 你的表名;

错误写法原因说明 #

1. select sum(amount->'value')::DECIMAL：sum函数入参是jsonb类型，没有对应支持的sum重载方法，转换逻辑写在sum外面完全不生效。
2. select sum(TO_NUMBER(amount->'value'))：to_number函数要求入参是text类型，传入jsonb类型会触发参数类型不匹配报错。

内容的提问来源于stack exchange，提问作者Joey Yi Zhao