CODEWITHSUNDEEP
sqlgoogle-bigquery
Kristian
Kristian

Reputation: 21830

BigQuery IF field exists THEN

I'm running a query over multiple tables unioned together over a particular time range.

In the past, a particular field did not exist in the "schema" but about halfway through that time range, the field began its existence and started getting populated with data.

Is there a way to conditionally select it if it exists, else arbitrarily populate a named field with a value?

like this:

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

Upvotes: 11

Views: 17131

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below should give you direction

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

Assuming you have a, b and c as a fields in your original table () - above can be used (see below) if you need to change 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>)

Upvotes: 13

Related Questions