How to use LISTAGG in AWS Athena?

Question

I want to use LISTAGG for querying in Amazon Athena. Are there any ways to aggregate data into list or string?

As Amazon Athena User Guide,

The grouping_expressions element can be any function (such as SUM, AVG, COUNT, etc.)

Answer 1

Option 1: array

with t(i) as (select 1 union all select 2 union all select 3) 
select  array_agg(i) as result 
from    t
;

  result
-----------
 [3, 2, 1]

---

Option 2: string

with t(i) as (select 1 union all select 2 union all select 3) 
select  array_join(array_agg(i),',') as result 
from    t
;

 result
--------
 1,3,2