How to return several values with Stored functions in BigQuery?

Question

Is there any way to return multiple results in bigquery (stored functions) I heard we should be able to do it with Array. Here is an example :

create function if not exists hw6.GetNumTherapistWorking(
    dateInput date
)
returns count1,count2,count3
as
(
    (select count1,count2,count3 from table....);
)

Answer 1

Consider below example to illustrate solution

create temp table mytable as
  select 1 count1, 2 count2, 3 count3, current_date as count_date union all 
  select 11, 12, 13, current_date - 3 union all 
  select 21, 22, 23, current_date - 5
;

# create function if not exists hw6.GetNumTherapistWorking(
create temp function GetNumTherapistWorking(dateInput date) as (
    array(select as struct count1,count2,count3 from mytable where count_date > dateInput)
);

select *
from unnest(GetNumTherapistWorking(current_date - 4));   

with output

Note: it uses temp UDF - but same works for permanent (stored) UDF - you will just need to use fully qualified UDF name - hw6.GetNumTherapistWorking