How to create multiple arrays with random numbers in BigQuery

Question

I have the following piece of code, in which I create 3 random arrays of length 5, 10, and 15 respectively.

with example as (
    select 1 as id, mod(cast(10*rand() as int64), 10) as random_array from unnest(generate_array(1, 5))
    union all select 2 as id, mod(cast(10*rand() as int64), 10) from unnest(generate_array(1, 10))
    union all select 3 as id, mod(cast(10*rand() as int64), 10) from unnest(generate_array(1, 15))
    )

select 
    id,
    array_agg(random_array) as nested_numbers
from unnesting_example
group by id

Is there a (smarter) way to do this by creating the arrays within the with clause? So, without using an extra select? Thanks.

Answer 1

You can use STRUCT

SELECT * FROM UNNEST([
  STRUCT(1 AS id, ARRAY((SELECT mod(cast(10*rand() as int64), 10) FROM UNNEST(GENERATE_ARRAY(1, 5)))) AS random_arr),
  STRUCT(2 AS id, ARRAY((SELECT mod(cast(10*rand() as int64), 10) FROM UNNEST(GENERATE_ARRAY(1, 10)))) AS random_arr),
  STRUCT(3 AS id, ARRAY((SELECT mod(cast(10*rand() as int64), 10) FROM UNNEST(GENERATE_ARRAY(1, 15)))) AS random_arr)
])

Output

id	random_arr
1	[2,2,7,3,5]
2	[8,5,8,2,1,1,7,3,9,8]
3	[9,1,7,0,2,1,6,6,7,8,8,0,2,4,1]

Answer 2

Consider below

select id, 
  array(
    select mod(cast(10 * rand() as int64), 10) 
    from unnest(generate_array(1, len) )
  ) as random_arr
from unnest([struct<id int64, len int64>(1,5), (2,10), (3,15)])       

with output