Postgresql jsonb_each function ignore empty column data when using with aggregate function with group by

Question

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?

That doesn't look like correct JSON.

Laurenz Albe
– Laurenz Albe

2022-06-14 13:13:36 +00:00
Commented Jun 14, 2022 at 13:13 — Laurenz Albe
– Laurenz Albe, Commented Jun 14, 2022 at 13:13
It's a ruby hash value actually.

Bisho Silwal
– Bisho Silwal

2022-06-14 14:18:39 +00:00
Commented Jun 14, 2022 at 14:18 — Bisho Silwal
– Bisho Silwal, Commented Jun 14, 2022 at 14:18

THX1138 · Accepted Answer · 2022-06-14 19:02:43Z

1

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.

edited Jun 14, 2022 at 19:02

answered Jun 14, 2022 at 18:53

THX1138

1,83418 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bisho Silwal Over a year ago

You are right, I don't have to use jsonb_each function, using SUM(COALESCE(discount->>'amount', '0')::numeric) as discount solved the issue. Thank you for the detailed explanation.

Collectives™ on Stack Overflow

Postgresql jsonb_each function ignore empty column data when using with aggregate function with group by

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related