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, '[email protected]' 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, '[email protected]' 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, '[email protected]' 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, '[email protected]' 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, '[email protected]' 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, '[email protected]' 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, '[email protected]' 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, '[email protected]' 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, '[email protected]' 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, '[email protected]' 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.



