Postgresql jsonb_each function ignore empty column data when using with aggregate function with grou-CodePudding

I am trying to get sum of discounts on booking, grouping by the resource id in the PostgreSQL query. FYI: discounts column is a jsonb column which store value eg: {'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

Problem jsonb_each function executes only for the not null discounts column, so all other rows are neglected: eg data:

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

But actual data without jsonb_each function returns:

[{"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}]

How can I execute all the records with the jsonb_each function?

CodePudding user response：

Assuming your discounts column object structure looks something like

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

You can use a LEFT JOIN LATERAL to sum all of the individual discount amounts for each booking_item. Then you can aggregate/sum by resource_id:

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

If the discounts column structure is literally just {"amount" : 100, "name" : "special_100_off", ... } you don't need to use jsonb_each and can instead do discounts -> 'amount' to get the amount value or null if the key isn't present.