Select records in BigQuery that have no record

2
votes

I have the results of a Google Vision API call in BigQuery in a table with a schema that looks like:

image STRING NULLABLE
...
labelAnnotations    RECORD  REPEATED    
labelAnnotations.description    STRING  NULLABLE    
...

I am able to get all images that have one or more labels with a query like:

  SELECT image,
      count(labelAnnotations.description) as n_labels
  FROM datasetid.tableid,
  UNNEST(labelAnnotations) as labelAnnotations
  GROUP BY 1

How do I get the image value when there is no labelAnnotations record for a particular image? ie. the API returned an empty labelAnnotations record, or no record at all.

I'm hoping this is obvious, but attempts to use WHERE labelAnnotations IS NULL failed.

1
google-bigquery

1 Answers

4
votes

How do I get the image value when there is no labelAnnotations record for a particular image?

Below is for BigQuery Standard SQL 

#standardSQL
SELECT image
FROM `datasetit.tableid`
WHERE ARRAY_LENGTH(labelAnnotations) = 0

you can test / play with above using dummy data as below 

#standardSQL
WITH `datasetit.tableid` AS (
  SELECT 'image1' image, [STRUCT<description STRING>('label1'), STRUCT('label2')] labelAnnotations UNION ALL
  SELECT 'image2', [] UNION ALL
  SELECT 'image3', [STRUCT<description STRING>('label3')] 
)
SELECT image
FROM `datasetit.tableid`
WHERE ARRAY_LENGTH(labelAnnotations) = 0