Athena scalar subquery issue

Question

I am working on athena . I have a table with two columns of interest besides other columns. They are id and account architect column. Both are string type .

This is how the table looks like now .

The end product should be ;

The query that i have is :

How should my query look like to get this end product .

Answer 1

This should solve your problem. Note Here I am using regexp_replace to remove all special characters like (comma,],[,double quotes).

select array_agg(distinct id),regexp_replace(username,'[^[:alnum:]'' '']') from 
(
select split_part("sto architect",',',1) as username,id from xyz_teams
Union 
select split_part("sto architect",',',2) as username,id from xyz_teams
) temp_tbl
group by regexp_replace(username,'[^[:alnum:]'' '']')