BigQuery报错：标量子查询返回多个元素及查询问题求助

Let’s break down exactly why you’re hitting this error and get your query working properly.

Why the error occurs #

Your main query uses a scalar subquery (select distinct dd from unnest(d.subcategory) dd) to populate the subcategory column. Scalar subqueries are meant to return exactly one value per row—but in your case, unnest(d.subcategory) spits out three values: Daily, Weekly, Monthly. BigQuery throws the error because it can’t cram multiple values into a single scalar field.

The fix: Properly unnest the array #

Instead of forcing multiple values into one column via a scalar subquery, you need to expand the array into separate rows using cross join unnest(). This will create one row for each element in your subcategory array, paired with the original date, isdepositor, and dau values.

Here’s the corrected query:

select 
  date,
  isdepositor,
  category,
  dd as subcategory,
  dau 
from( 
  select 
    date,
    isdepositor,
    'Level' as category,
    array(select 'Daily' union all select 'Weekly' union all select 'Monthly') subcategory,
    dau 
  from DWH.vT_DAU 
) d
cross join unnest(d.subcategory) dd

What changed? #

• Replaced the problematic scalar subquery with cross join unnest(d.subcategory) dd: This takes each element in the subcategory array and turns it into its own row.
• Kept the subcategory column name by aliasing dd to match your original intent.

If you’re wrapping this logic into a custom function, apply the same unnesting pattern inside the function to avoid scalar subquery issues. For example, a function that generates the subcategory array would use cross join unnest() to ensure each value is returned as a separate row, rather than trying to stuff multiple values into a single scalar output.

内容的提问来源于stack exchange，提问作者Porada Kev