如何让PostgreSQL查询返回空JSON字段的0值求和结果？

场景与问题 #

现有表tableA结构及数据如下：

______________________________________________________
|companyId |    detailsJson                          |
|----------| ----------------------------------------|
|12        |{"dataKeyOne":1.10, "dataKeyTwo":1.20}   |
|123       |{"dataKeyFour":2.12, "dataKeySeven":1.18}|
|134       |    {}                                   |
|342       |    {}                                   |
______________________________________________________

当前使用的查询语句：

select companyId, sum(value::float) as sum
from tableA, jsonb_each_text(detailsJson)
group by companyId;

得到的输出结果：

|companyId |    sum|
|----------|-------|
|12        |  2.30 |
|123       |  3.30 |

需要实现的效果：当detailsJson为空对象时，对应的companyId的sum值返回0.0，期望输出：

|companyId |    sum|
|----------|-------|
|12        |  2.30 |
|123       |  3.30 |
|134       |  0.0  |
|342       |  0.0  | 

解决方案 #

原查询用了隐式内连接（逗号分隔），当detailsJson是空对象时，jsonb_each_text()返回空结果集，导致这些行被过滤。改用LEFT JOIN LATERAL保留所有companyId行，再用COALESCE将sum的NULL值替换为0.0即可。

修改后的查询语句：

select 
    companyId, 
    COALESCE(sum(value::float), 0.0) as sum
from tableA
left join lateral jsonb_each_text(detailsJson) on true
group by companyId;

关键说明 #

• LEFT JOIN LATERAL：确保即使jsonb_each_text()返回空，也会保留tableA中的每一行数据。
• COALESCE(sum(value::float), 0.0)：当sum计算结果为NULL（即没有可求和的JSON键值对）时，自动替换为0.0。

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