CODEWITHSUNDEEP
google-bigquery
Quentin Sommer
Quentin Sommer

Reputation: 313

How to conditionally aggregate a column based on another

a row of my data looks like this

[someId, someBool, someInt]

I'm looking for a way to aggregate someInt (to put them in an array specifically).

I use a GROUP BY clause to group by the someId field, then I can aggregate all the someInt using ARRAY_AGG but I only want to include rows where someBool=TRUE. How to approach this the right way ?

PS: It might be relevant to note what I got several booleans like someBool and would like to output to a different array each time

Upvotes: 3

Views: 3730

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

You can use ARRAY_AGG with IGNORE NULLS, e.g.:

ARRAY_AGG(IF(someBool IS NOT TRUE, NULL, someId) IGNORE NULLS)

This will only aggregate the IDs for which someBool is true. If you have multiple boolean columns that you want to use in the condition, you can AND them together or use a CASE WHEN ... or whatever other kind of condition you want that produces NULL in order to exclude a value.

Upvotes: 8

Related Questions