Hi I was not able to find this anywhere.
How do I do a collect_set in Google BigQuery?
I have a query already written in Big query
select user_id, steps, from table
I need to put all of the steps for each user_id into an array like collect_set would.
The equivalent for group_concat in BigQuery Standard SQL is string_agg. So, the equivalent query could be written as:
select user_id, string_agg(steps) as steps
from table
group by user_id
Refer the docs here - https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#string_agg
You may get the result you want with ARRAY_AGG(DISTINCT ), for instance
SELECT
ARRAY_AGG(DISTINCT value)
FROM
UNNEST([1, 1, 2, 3, 3, 3, 5]) AS value
gives you an array with [1, 2, 3, 5]