0

I have a table like this:

enter image description here

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

enter image description here

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:

enter image description here

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

Thank you.

1 Answer 1

1

I'd wrap another SELECT ... GROUP BY around the original query like:

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(lead,name,phone,mail)) FROM (
    SELECT id,valuation,name,phone,mail, ARRAY_AGG(STRUCT(origin,date_lead,type,id_cus)) as lead 
    FROM sample
    GROUP BY id,valuation,name,phone,mail
)
GROUP BY id,valuation

enter image description here

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.