PostgreSQL中jsonb_each配合group by聚合时空值行被忽略如何解决

问题原因 #

原查询中使用逗号拼接booking_items表与jsonb_each(discount)的返回结果，属于隐式内连接。当discount字段为NULL、空jsonb对象时，jsonb_each不会返回任何行，对应booking_items的记录会被直接过滤，最终仅返回存在有效discount值的resource_id统计结果。

修正方案 #

方案1：保留jsonb_each写法（适配多键场景） #

使用LEFT JOIN LATERAL做左连接，保留booking_items全量记录，即使jsonb_each无返回行也不会丢弃主表数据：

select 
  resource_id, 
  sum((dis.value)::numeric) as dis_value,
  sum(item_total::numeric) as g_total,
  sum(sub_total::numeric) as n_total, 
  sum(total::numeric) as total 
from booking_items
left join lateral jsonb_each(discount) as dis on true
group by resource_id;

注：如果jsonb_each返回的value是jsonb数值类型，建议先转文本再转数值避免类型转换报错，即(dis.value #>> '{}')::numeric

方案2：直接提取固定键（适配当前单amount键场景，性能更优） #

根据给出的discount存储结构，所有折扣值都存在amount键下，不需要遍历所有键值对，直接提取目标键即可，天然不会过滤空记录：

select 
  resource_id, 
  sum((discount ->> 'amount')::numeric) as dis_value,
  sum(item_total::numeric) as g_total,
  sum(sub_total::numeric) as n_total, 
  sum(total::numeric) as total 
from booking_items
group by resource_id;

补充说明 #

• 两种写法中，当discount为空、不存在amount键时，提取到的折扣值为NULL，sum函数会自动忽略NULL值，等价于该条记录折扣计0，不会影响总和计算结果。
• 如果需要明确语义，可以用coalesce把NULL折扣转为0，例如sum(coalesce((discount ->> 'amount')::numeric, 0)) as dis_value，统计结果和直接sum无差异。

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