Bigquery Leagacy SQL vs Standard SQL - Array Queries

Bigquery supports both Standard and Legacy SQL, below is the example of how to migrate array related SQLs from legacy to standard SQL.

When we use array type of data in our tables then we should follow below Bigquery Syntaxes:

Legacy

SELECT date, id, code, country, gender, dob, city, location, product.category

FROM  TABLE_DATE_RANGE([PROJECT:DATASET.TABLE_],DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'),DATE_ADD(CURRENT_TIMESTAMP(), -0, 'DAY'))

Standard

SELECT date, id, code, country, gender, dob, city, location, prod.category

FROM `PROJECT.DATASET.TABLE_*`,

UNNEST(product) AS prod

WHERE (_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL -1 day))

AND FORMAT_DATE("%Y%m%d", CURRENT_DATE()))