使用函数json_each_text()，它给出对(key, value)，将values转换为整数，并将它们按keys分组求和。最后，将结果聚合为json：

select json_object_agg(key, sum)
from (
    select key, sum(value::int)
    from my_table 
    cross join json_each_text(totalpetsjson)
    where myuid in ('john','andy') 
    group by key
    ) s

                     json_object_agg                     
---------------------------------------------------------
 { "fish" : 144, "lizards" : 4, "cats" : 8, "dogs" : 5 }
(1 row)

在db<>fiddle.中测试

在klin之前的anwser的基础上，如果你不想整张表，而是想保持分组，那么这种方法可以扩展：

SELECT x, json_object_agg(key, sum)
FROM (
    SELECT x, key, sum(value::INT) AS sum
    FROM (
        VALUES (1, '{
          "a": 10,
          "b": 11
        }'::JSONB),
        (1, '{
          "b": 12,
          "c": 13
        }'::JSONB),
        (2, '{
          "b": 14,
          "c": 15
        }'::JSONB)
    ) AS mytable(x, y)
             CROSS JOIN jsonb_each_text(Y)
    GROUP BY 1, 2
) x
GROUP BY 1
ORDER BY 1;

结果：

1,"{ ""c"" : 13, ""b"" : 23, ""a"" : 10 }"
2,"{ ""c"" : 15, ""b"" : 14 }"