IF BigQuery field exists THEN

Question

I am performing a query on several tables joined together in a specific time range.

In the past, a specific “field” did not exist in the “scheme,” but about halfway to this time range, the field began to exist and began to fill with data.

Is there a way to conditionally select it, if it exists, otherwise arbitrarily fill the named field with a value?

like this:

SELECT (CASE WHEN exists(my_field) THEN my_field ELSE "0" END) as "my_field" FROM <somewhere> 

Answer 1

The direction should be indicated below.

 SELECT * FROM (SELECT * FROM <somewhere w/o my_field>), (SELECT * FROM <somewhere with my_field>) 

Assuming you have a, b, and c as fields in your original table () - above, you can use (see below) if you need to change the missing values ​​from NULL to 0:

 SELECT a, b, c, COALESCE(my_field, 0) as my_field FROM (SELECT * FROM <somewhere w/o my_field>), (SELECT * FROM <somewhere with my_field>) 

Answer 2

If I'm not mistaken, then the default fields are zero by default, so this should do the trick:

 select ifnull(some_field, "default value") as field from <somewhere>; 

Answer 3

I could not use the @Mikhail Berlyant solution, as I had an ambiguous column problem. Did someone intervene in the problem?

 WITH all_fields AS (SELECT "a" first, "a" second, "a" third), not_all_fields AS (SELECT "b" first, "b" third) SELECT first, second, third FROM (SELECT * FROM all_fields), (SELECT * FROM not_all_fields) 

Error: Column name first is ambiguous at [6:8]