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()))

Big Query Legacy Vs Standard Date Ranges Query

Bigquery supports both Standard and Legacy SQL, below are the few tips how we can migrate SQLs from legacy to standard SQL.

Legacy

For date shard table:

SELECT * FROM

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

For partitioned table use

_PARTITIONTIME BETWEEN TIMESTAMP(DATE_ADD(CURRENT_TIMESTAMP(), -4, 'Day'))

AND TIMESTAMP(CURRENT_TIMESTAMP())

Standard

For date shard table:

SELECT * FROM

FROM `PROJECT.DATASET.TABLE_*`

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

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

For partitioned table replace _TABLE_SUFFIX with DATE(_PARTITIONTIME)