Search code examples
google-bigquery

BigQuery check entire table for null values

Not sure if a reproducible example is necessary here. I have a big-ish and wide-ish table in BigQuery (10K rows x 100 cols) and I would like to know if any columns have null values, and how many null values there are. Is there a query that I can run that would return a 1-row table indicating the number of null values in each column, that doesn't require 100 ifnull calls?

Thanks!

Solution

  • Below is for BigQuery Standard SQL 

    #standardSQL
SELECT col_name, COUNT(1) nulls_count
FROM `project.dataset.table` t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"(\w+)":null')) col_name
GROUP BY col_name

    Instead of returning just one row - it returns those column which have NULL in them - each column and count in separate row - like in below example 

    Row col_name    nulls_count  
1   col_a       21   
2   col_d       12