CODEWITHSUNDEEP
google-bigquery
Rhoderick Medina
Rhoderick Medina

Reputation: 133

BigQuery equivalent of COALESCE()?

I'm in the process of converting some aggregate queries from Postgres to our new architecture in BigQuery. Is there an equivalent of COALESCE() in BigQuery?

Currently, I am converting a Postgres query statement like 

coalesce(column1,'DEFAULT')

to

CASE
  WHEN column1 IS NOT NULL
     THEN column1
   ELSE 'DEFAULT'
END AS column1

which seems easy enough.

However converting a Postgres query statement with nested coalesce statements like

 count(distinct coalesce(
                coalesce(
                coalesce(column1,column2),
                                 column3),
                                 column4)))

would get much more messy if I used CASE statements all over the place, and also seems like the wrong thing to do.

Does BigQuery have a method equivilent to COALESCE(), or am I stuck writing the whole CASE statement equivalent?

Upvotes: 13

Views: 22419

Answers (1)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14004

You can use IFNULL function in BigQuery, which can be nested like that:

select ifnull(column1,
              ifnull(column2,'DEFAULT')) 
from 
(select string(NULL) as column1, 'y' as column2)

P.S. But omission of COALESCE in BigQuery is an oversight, I will fix it.

Update: As of 4/16/2015, COALESCE is available in BigQuery. https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#conditional_expressions

Upvotes: 27

Related Questions