GCP Bigquery – query empty values from a record type value

I’m trying to query all resources that has empty records on a specific column but I’m unable to make it work. Here’s the query that I’m using:

SELECT
  service.description,
  project.labels,
  cost AS cost
FROM
  `xxxxxx.xxxxx.xxxx.xxxx`
WHERE
  service.description = 'BigQuery' ;

Here’s the results:

As you can see, I’m getting everything with that query, but as mentioned, I’m looking to get resources with empty records only for example record 229,230 so on.

Worth to mention that schema for the column I’m trying to query is:

project.labels  RECORD  REPEATED

The above was mentioned because I tried using several combinations of WHERE but everything ends up in error.

Answer

To identify empty repeated record – you can use ARRAY_LENGTH in WHERE clause like in below example

WHERE ARRAY_LENGTH(project.labels) = 0

Advertisement

Answer