Presto equivalent of MySQL group_concat

Question

I'm new to Presto and looking to get the same functionality as the group_concat function in MySQL. Are the following two equivalent? If not, any suggestions for how I can recreate the group_concat functionality in Presto?

MySQL:

select 
  a,
  group_concat(b separator ',')
from table
group by a

Presto:

select 
  a,
  array_join(array_agg(b), ',')
from table
group by a

(Found this as a suggested Presto workaround here when searching group_concat functionality.)

score 50 · Answer 1 · answered Feb 24 '18 at 12:41

50

Try using this in place of group_concat in Presto ::

select 
  a,
  array_join(array_agg(b), ',')
from table
group by a

answered Feb 24 '18 at 12:41

Rahul Ahuja

501
4
2

2

For those who see an unexpected order in the aggregation, you can use `array_agg(x ORDER BY y)` – spencer.sm Dec 02 '21 at 06:03

score 30 · Answer 2 · answered Mar 20 '18 at 19:51

30

Also, if you're looking for unique values only – an equivalent to group_concat(distinct ... separator ', ') – try this:

array_join(array_distinct(array_agg(...)), ', ')

answered Mar 20 '18 at 19:51

Jacob Rose

430
4
6

I've found that (in this particular case of achieving *distinct* `group_concat`) using nested queries for manually taking `DISTINCT` values performs better than the `array_distinct` **UDF**. Not sure that this would be *universally true* though – y2k-shubham Sep 18 '18 at 06:09
You can simplify this to `array_agg(distinct b)` and leave off the `array_distanct()` part – swdev Jan 26 '23 at 01:00

score 4 · Answer 3 · answered Jun 20 '17 at 18:33

There's no function as of this answer, though the feature has been requested.

The closest equivalent is mentioned in your question.

WITH tmp AS (
SELECT 'hey' AS str1
UNION ALL
SELECT ' there'
)
SELECT array_join(array_agg(str1), ',', '') AS joined
FROM tmp

Presto equivalent of MySQL group_concat

3 Answers3

Linked

Related