BigQuery add condition on Repeated fields in select statement

Question

I have a table in BigQuery which contains a repeated field

I need to get all the rows which have NOT NULL values in that repeated fields.

for example, consider this table schema

{
   "name": "STRING",
   "age" "NUMBER",
   "courses": "REPEATED:STRING"
}

what is the query which gets all the users who have at least 1 NOT NULL course

Answer 1

Below example for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'mike' name, 17 age, [] courses UNION ALL
  SELECT 'nick', 18, ['bigquery', 'mysql'] UNION ALL
  SELECT 'mishelle', 19, ['bigquery']
)
SELECT *
FROM `project.dataset.table`
WHERE ARRAY_LENGTH(courses) > 0   

with result