scalar-subquery

Athena scalar subquery issue

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 .

enter image description here

This is how the table looks like now .

The end product should be ;

enter image description here

The query that i have is : enter image description here

enter image description here

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

Solution

  • 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:]'' '']')