CODEWITHSUNDEEP
google-bigquery
JIANG
JIANG

Reputation: 1877

How To Query A Repeated String Field With Empty Value [] in BigQuery?

New to the BigQuery, I have a repeated field in BigQuery, like this

myTable
{
"id": 12345
"myNestedStringArrayField": []
}

How can I query all rows with the myNestedStringArrayField value is empty? I tried using myNestedStringArrayField is null, but return no results, I know I have rows that have [] as the value. I also tried using the = '[]' , but the query edit throws an error.

Thank you in advance.

Upvotes: 2

Views: 2940

Answers (1)

Cylldby
Cylldby

Reputation: 1978

You can try using ARRAY_LENGTH, all rows you are seeking have a myNestedStringArrayField with a length of zero:

WITH sample AS(
    SELECT STRUCT("12345" AS id, [] AS myNestedStringArrayField) AS myTable
)

SELECT *
FROM sample 
WHERE ARRAY_LENGTH(myTable.myNestedStringArrayField) = 0

This returns:

enter image description here

Upvotes: 3

Related Questions