I have a table that is using ARRAY_AGG on a few STRUCTs. So far, none of the values needed to be aggregated.

I added a new STRUCT and added it to a ARRAY_AGG but cannot understand how to sum a field in this new STRUCT.

There are currently 2 STRUCTs using ARRAY_AGG, professor and class. I am trying to add a 3rd STRUCT, student that sums the student.credits field.

My table currently looks something like this

This is what I'm trying to make it look like (I want to sum the student.credits field)

Any advice is greatly appreciated, I've been searching for a few days but haven't found an answer.

CodePudding user response：

Consider the below query:

with sample_data as (
  select 1 as school_id, struct('M' as gender, 25 as age) as professor, struct('Math'as type, 'Algebra' as name) as class, struct('John' as name, 4 as credits) as student
  union all select 1 as school_id, struct('M' as gender, 25 as age) as professor, struct('Math'as type, 'Algebra' as name) as class, struct('John' as name, 4 as credits) as student
  union all select 1 as school_id, struct('M' as gender, 25 as age) as professor, struct('Math'as type, 'Algebra' as name) as class, struct('Mary' as name, 5 as credits) as student
  union all select 1 as school_id, struct('M' as gender, 25 as age) as professor, struct('Math'as type, 'Algebra' as name) as class, struct('Mary' as name, 6 as credits) as student
  union all select 1 as school_id, struct('M' as gender, 25 as age) as professor, struct('Math'as type, 'Algebra' as name) as class, struct('Mary' as name, 2 as credits) as student
),
agg_data as (
select 
  school_id, 
  array_agg(professor) as professor,
  array_agg(class) as class,
  array_agg(student) as student,
from sample_data
group by school_id
),

---------------
-- Query above is responsible for generating sample data
---------------

sum_data as (
select 
  school_id,
  professor[offset(index)].age,
  professor[offset(index)].gender,
  class[offset(index)].name,
  class[offset(index)].type,
  student[offset(index)].name as s_name,
  sum(student[offset(index)].credits) as credits,

from agg_data,
  unnest(generate_array(0,array_length(class)-1)) as index
group by 1,2,3,4,5,6
)

select 
  school_id,
  array_agg(struct(age,gender)) as professor,
  array_agg(struct(name,type)) as class,
  array_agg(struct(s_name,credits)) as student
from sum_data
  group by school_id

Output: