unnest() used, but still cannot access field text on a value with type ARRAY<STRUCT<text STRING, language STRING>>, big query

Question

I'm using bigquery to search for patents that contain the word 'metal' in the title. My query:

Then I got the following error message:

I checked the data structure for title_localized:

What I'm doing wrong?

Answer 1

You can use UNNEST this way to query nested fields:

SELECT
  DISTINCT country_code
FROM
  `patents-public-data.patents.publications`,
  UNNEST(title_localized) AS t
WHERE
  t.text LIKE '%metal%'