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?

Lowering

Answer 1

Try using ARRAY_JOIN with ARRAY_AGG:

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

Dulosis

ARRAY_JOIN(ARRAY_AGG( this changes the order. Do you know how to solve it?, In this question for a1 I want to maintain the order so the contact should be action1,action2 But many times Im getting the misplaced order like action2,action1 – Glabrous

If you like to keep the order please refer to array_agg(x ORDER BY y DESC) – Joijoice

@Joijoice that is truly golden, many thank you's! – Airspeed

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  

Wedlock