CODEWITHSUNDEEP
google-bigquery
Tomas Jansson
Tomas Jansson

Reputation: 23462

Is it possible to do a bigquery wild card select on tables where the number of columns differ?

In version on of the schema my table X_yyyyMMdd only had one field a. In the next version I decided to add one field, b.

How do I go around doing a wild card select against X_* now where all the columns in tables of the first version get a default value of column b.

What I would like to write is:

SELECT a, IF_FIELD_EXISTS(b, default_value) FROM X_*

Upvotes: 0

Views: 36

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Yes. Below should work!

#standardSQL
SELECT a, IFNULL(b, default_value) as b
FROM `project.dataset.X_*`

In case if b is ARRAY (repeatable field) - use below 

#standardSQL
SELECT 
    a, 
    CASE 
        WHEN ARRAY_LENGTH(b)=0 THEN [default_value]
        ELSE b 
    END AS b
FROM `project.dataset.X_*`

Upvotes: 2

Related Questions