'BigQuery ARRAY_TO_STRING based on condition in non-array field

I have a table that I query like this...

select *
from table
where productId = 'abc123'

Which returns 2 rows (even though the productId is unique) because one of the columns (orderName) is an Array...

**productId, productName, created, featureCount, orderName**
abc123, someProductName, 2020-01-01, 12, someOrderName
      ,                ,           ,   , someOtherOrderName

I'm not sure whether the missing values in the 2nd row are empty strings or nulls because of the way the orderName array expands my search results but I want to now run a query like this...

select productName, ARRAY_TO_STRING(orderName,'-')
from table
where productId = 'abc123'
and ifnull(featureCount,0) > 0

But this query returns...

someProductName, someOrderName-someOtherOrderName

i.e. both array values came back even though I specified a condition of featureCount>0.

I'm sure I'm missing something very basic about how Arrays function in BigQuery but from Google's ARRAY_TO_STRING documentation I don't see any way to add a condition to the extracting of ARRAY values. Appreciate any thoughts on the best way to go about this.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source