Combine rows into a list in PrestoSQL

Question

Currently, my table has three different fields, id1, id2 and actions. action is of type string. For example, my table looks something like the table given below:

id1   |   id2  |   actions
---------------------------
"a1"      "a2"     "action1"
"b1"      "b2"     "action2"
"a1"      "a2"     "action3"

If the values of id1 and also the valuesid2 are same for any number of rows, I want to combine those rows so that the actions field becomes a list of string. If none of the rows have same values for id1 and same values for id2, I want to still convert the actions fields as a list but only with one string. For example, the output of the query should look something like the following:

id1   |   id2  |   actions
---------------------------
"a1"      "a2"     ["action1", "action3"]
"b1"      "b2"     ["action2"]

I know some basics of Presto and can join columns based on conditions but was not sure if this can be achieved with query. If this can be achieved, what is a good approach to move forward with the implementation of this logic?

Answer 1

Try using ARRAY_JOIN with ARRAY_AGG:

SELECT
    id1,
    id2,
    ARRAY_JOIN(ARRAY_AGG(actions), ',') actions
FROM yourTable
GROUP BY
    id1,
    id2;

Answer 2

If you want the result as an array you can use flatten instead of array_join:

select   
    id1,  id2,   flatten(array_agg(actions)) as actions  
from table  
group by id1, id2