How to get array of values in GROUP BY clause using BigQuery?

Question

This question is modelled on How to get list of values in GROUP_BY clause? - which is a sybase question, not applicable in BigQuery context.

---

If I have data like this in a table

id   data
--   ----
1    1
1    2
1    3
2    4
2    5
3    6
3    4

How do I get results like this in a query?

id   data
--   ----
1    1, 2, 3
2    4, 5
3    6, 4

where 1, 2, 3, 4, 5 and 6,4 are arrays.

Answer 1

try this

SELECT
  id,
  ARRAY_AGG(data)
FROM
  table_name
GROUP BY
  id