Multiple array_agg in bigquery

I have a table like this:

I wanted to group information in arrays based on the first two variables and thats what I did

WITH sample as (
SELECT 1023 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'chop' as type,
'c1023' as id_cus, 'julian' as name, '12345' as phone, 'julian@gmail.com' as    mail
UNION ALL
SELECT 1023 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'ext' as type,
'c1023' as id_cus, 'julian' as name, '12345' as phone, 'julian@gmail.com' as    mail
UNION ALL
SELECT 1021 as id,10 as valuation,'inegi' as    origin, 2021-01-01 as date_lead,'ext' as type,
'in-2020' as id_cus, 'lucian' as name, '12345' as phone, 'lucian@gmail.com' as  mail
UNION ALL
SELECT 1021 as id,10 as valuation,'inegi' as    origin, 2021-01-01 as date_lead,'ext' as type,
'in-2020' as id_cus, 'lucian' as name, '12345' as phone, 'lucian@gmail.com' as  mail
UNION ALL
SELECT 1021 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'int' as type,
'c1021' as id_cus, 'lucian' as name, '12345' as phone, 'lucian@gmail.com' as    mail
UNION ALL
SELECT 1021 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'int' as type,
'c1021' as id_cus, 'lucas' as name, '202342' as phone, 'lucas@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'type' as type,
'c1040' as id_cus, 'julieta' as name, '202112' as phone, 'julieta@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'chop' as type,
'c1040' as id_cus, 'julieta' as name, '202112' as phone, 'julieta@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'rad' as type,
'c1040' as id_cus, 'julieta' as name, '202112' as phone, 'julieta@gmail.com' as mail
UNION ALL
SELECT 1040 as id,10 as valuation,'tlv' as  origin, 2021-01-01 as date_lead,'uls' as type,
'c1040' as id_cus, 'julieta' as name, '123123' as phone, 'julieta@gmail.com' as mail
)
SELECT id,valuation,ARRAY_AGG(STRUCT(origin,date_lead,type,id_cus,name,phone,mail)) as lead 
FROM sample
GROUP BY id,valuation

The problem here is that I noticed that I have a lot of repeteated values in the last three variables (name,phone and mail). I would like to group them as well but I am not sure how to do that. I noticed that I can't create an additional array_agg instide of the first one.

I am looking to get something like this:

There is anyway to do something like this? WHat would you do?

Thank you.

from Recent Questions - Stack Overflow https://ift.tt/38mNcNS
https://ift.tt/2WshYSK