collect_set for Google bigquery

Question

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.

Answer 1

try using NEST() function:

SELECT user_id, NEST(steps) AS steps 
FROM table
GROUP BY user_id

See below for some tricks on how to make NEST work to be able to write to table
BigQuery NEST() returns 'Error: An internal error occurred'

Another option would be to just build list of steps as string using GROUP_CONCAT:

SELECT user_id, GROUP_CONCAT(steps) AS steps 
FROM table
GROUP BY user_id

Answer 2

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

Answer 3

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]