BigQuery - Querying Nested Fields in Standard SQL

Question

I have the following Schema in BigQuery:

The whole table looks as follows:

SELECT * FROM `...nested`

I now simply want to query all rows that have a comment which is titled "Hello", and I'm breaking my head how to do this.

SELECT * FROM `...nested` WHERE comments.title = 'Hello'
# Error: Cannot access field title on a value with type ARRAY<STRUCT<title STRING, message STRING>> at [1:69]

SELECT * FROM `...nested` WHERE comments.title IN ('Hello')
# Error: Cannot access field title on a value with type ARRAY<STRUCT<title STRING, message STRING>> at [1:69]

comments.title CONTAINS "Hello" seems to be available legacy SQL, so I'm wondering what the equivalent is.

Answer 1

Try this:

SELECT *
FROM dataset.nested
WHERE EXISTS (
  SELECT 1 FROM UNNEST(comments)
  WHERE title = 'Hello'
)

This returns all rows where the title of at least one comment is Hello. You can also use LIKE in place of the equality to look for substrings, for example. For more reading on working with arrays, see the relevant documentation.