How to aggregate json fields when using GROUP BY clause in postgres?

Question

I have the following table structure in my Postgres DB (v12.0)

id | pieces | item_id | material_detail
---|--------|---------|-----------------
1  | 10     | 2       | [{"material_id":1,"pieces":10},{"material_id":2,"pieces":20},{"material_id":3,"pieces":30}]

2  | 20     | 2       | [{"material_id":1,"pieces":40}
3  | 30     | 3       | [{"material_id":1,"pieces":20},{"material_id":3,"pieces":30}

I am using GROUP BY query for this records, like below

SELECT SUM(PIECES) FROM detail_table GROUP BY item_id HAVING item_id =2

Using which I will get the total pieces as 30. But how could I get the count of total pieces from material_detail group by material_id.

I want result something like this

 pieces |  material_detail
 -------| ------------------
  30    |  [{"material_id":1,"pieces":50},{"material_id":2,"pieces":20},{"material_id":3,"pieces":30}]

As I am from MySQL background, I don't know how to achieve this with JSON fields in Postgres.

Note: material_detail column is of JSONB type.

Could you write the query if the schema was properly normalised, i.e. with material_details in their own table? — Bergi
– Bergi, Commented May 28, 2020 at 9:07
Please do not crosspost also asked here: dba.stackexchange.com/questions/268016 — user330315
– user330315, Commented May 28, 2020 at 9:17

user330315 · Accepted Answer · 2020-05-28 09:41:11Z

4

You are aggregating on two different levels. I can't think of a solution that wouldn't need two separate aggregation steps. Additionally to aggregate the material information all arrays of the item_id have to be unnested first, before the actual pieces value can be aggregated for each material_id. Then this has to be aggregated back into a JSON array.

with pieces as (

  -- the basic aggregation for the "detail pieces"
  select dt.item_id, sum(dt.pieces) as pieces
  from detail_table dt
  where dt.item_id = 2
  group by dt.item_id

), details as (

  -- normalize the material information and aggregate the pieces per material_id
  select dt.item_id, (m.detail -> 'material_id')::int as material_id, sum((m.detail -> 'pieces')::int) as pieces
  from detail_table dt
    cross join lateral jsonb_array_elements(dt.material_detail) as m(detail)
  where dt.item_id in (select item_id from pieces) --<< don't aggregate too much    
  group by dt.item_id, material_id

), material as (

  -- now de-normalize the material aggregation back into a single JSON array
  -- for each item_id
  select item_id, jsonb_agg(to_jsonb(d) - 'item_id') as material_detail
  from details d
  group by item_id

)
-- join both results together
select p.item_id, p.pieces, m.material_detail
from pieces p
  join material m on m.item_id = p.item_id
;

Online example

answered May 28, 2020 at 9:41

user330315

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

4 Comments

Sunny Over a year ago

what is the use of cross join lateral here?

user330315 Over a year ago

@Sunny: it's required to turn the single JSON into rows. I prefer to write an explicit cross join lateral rather then an implicit from detail_table dt, jsonb_array_elements(...)

Sunny Over a year ago

is it require to include the item_id when normalizing the material info. I guess where and group by clause in that section will return the same records as that of returned in "pieces" and then simply we can jsonb_agg(d) instead of jsonb_agg(to_jsonb(d) - 'item_id') and also I guess join is not required. between pieces and material in the final query.

user330315 Over a year ago

If you ever want to do that for more then one item_id, then yes it's required.

Collectives™ on Stack Overflow

How to aggregate json fields when using GROUP BY clause in postgres?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related