Postgresql jsonb_each函数忽略空列数据与聚合函数与group by

Question

我试图获得预订折扣的总和，在PostgreSQL查询中按资源id分组。仅供参考:折扣列是存储值的jsonb列，例如:{'amount' =>100}

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, jsonb_each(discount) as dis
group by resource_id

问题jsonb_each函数只执行非空折扣列，所以所有其他行被忽略:如数据:

[{"resource_id"=>19, "dis_value"=>0.1e3, "g_total"=>nil, "n_total"=>0.0, "total"=>nil}]

但实际数据没有jsonb_each函数返回:

[{"resource_id"=>42, "qty"=>2, "g_total"=>nil, "n_total"=>0.1905e4, "total"=>nil},
{"resource_id"=>54, "qty"=>2, "g_total"=>nil, "n_total"=>0.12e4, "total"=>nil},
{"resource_id"=>19, "dis_value"=>0.1e3, "g_total"=>nil, "n_total"=>0.0, "total"=>nil}
{"resource_id"=>8, "qty"=>7, "g_total"=>nil, "n_total"=>0.0, "total"=>nil}]

如何使用jsonb_each函数执行所有记录?

Answer 1

假设您的discounts列对象结构看起来像

{
"disc1" : {
"amount" : "5",
...
},
"disc2" : {
"amount" : "10",
...
} 
}

您可以使用LEFT JOIN LATERAL来对每个booking_item的所有单独折扣金额求和。然后可以通过resource_id:

聚合/sum。

WITH booking_items(id,resource_id,item_total,sub_total,total,discounts) as (
(VALUES
(1,1,8,70,80,jsonb_build_object('disc1',jsonb_build_object('amount',5),'disc2',jsonb_build_object('amount',10))),
(1,1,3,90,100,jsonb_build_object()),
(1,3,1,7,8,null)
)
)
SELECT
bi.resource_id,
coalesce(sum(discs.total_disc::numeric),0) as dis_value,
sum(bi.item_total::numeric) as g_total,
sum(bi.sub_total::numeric) as n_total,
sum(bi.total::numeric) as total
from
booking_items bi
-- The LATERAL here means roughly "perform the following subquery for each row of bi".
-- Since it's a LEFT JOIN, no records of bi will be eliminated.
LEFT JOIN LATERAL (
SELECT
-- retrieve the value of the amount key, and sum
sum((disc -> 'amount')::numeric) as total_disc
FROM
--produce records for each discount entry in the discounts object
jsonb_each(bi.discounts) discs(disc_name,disc)
GROUP BY
bi.id
) discs ON TRUE
GROUP BY resource_id

如果discounts列结构实际上只是{"amount" : 100, "name" : "special_100_off", ... }，则不需要使用jsonb_each，可以使用discounts -> 'amount'来获取金额值，如果键不存在，则使用null。