PostgreSQL如何对JSON对象内所有嵌套的sum字段值求和

场景说明 #

• 数据库环境：PostgreSQL
• 需求：对JSON类型字段存储的键值对结构对象，累加所有一级子节点下的sum字段数值，基于给定示例数据的正确计算结果为50
• 问题：常规JSON数组聚合求和方法仅适配数组结构，直接套用在键值对对象上会执行报错

示例待处理JSON数据 #

'{
    "1": {
        "sum": 5
    },
    "2": {
        "sum": 10
    },
    "2728": {
        "sum": 30
    },
    "2729": {
        "sum": 5
    }
}'

错误写法问题 #

之前尝试的写法存在两个核心问题：

• 使用了仅支持JSON数组遍历的json_array_elements函数，无法处理键值对结构的JSON对象
• 固定指定了2729节点做计算，没有遍历所有顶层子节点，结果不符合全量累加的需求

错误写法参考：

WITH x AS( SELECT
'{
    "1": {
        "sum": 5,
    },
    "2": {
        "sum": 10,
    },
    "2728": {
        "sum": 30,
    },
    "2729": {
        "sum": 1410,
    }
}'::json as y),
sums AS(
SELECT json_array_elements(y->'2729') as j FROM x)
SELECT sum((j->>'sum')::int) FROM sums;

正确实现SQL #

核心是使用json_each函数遍历JSON对象的所有键值对，提取每个子节点的sum值转数值后聚合求和：

WITH x AS( 
    SELECT
    '{
        "1": {
            "sum": 5
        },
        "2": {
            "sum": 10
        },
        "2728": {
            "sum": 30
        },
        "2729": {
            "sum": 5
        }
    }'::json as y
),
sums AS(
    SELECT value AS j FROM x, json_each(x.y)
)
SELECT sum((j->>'sum')::int) AS total_sum FROM sums;

适配说明 #

• 如果存储字段为jsonb类型，将json_each替换为jsonb_each即可正常使用
• 如果sum字段存储的是小数，将类型转换::int替换为::numeric即可避免精度丢失
• 执行上述SQL针对示例数据的返回结果为50，符合预期。

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