Google BigQuery - parsing string to JSON object fetching field and evalaute as string-CodePudding

I'm parsing a Google BigQuery table patents-public-data.uspto_peds.backfile and the fields are JSON formatted strings.

I can parse the field to SELECT only one of the values like so:

PARSE_JSON(json_strong)['attribute'][0]['attribute2'] as aliasName

But in the WHERE clauses I want to also limit the returns to only values that have a date after a certain value. When I try was seems reasonable with that:

WHERE
EXTRACT(YEAR FROM DATE(PARSE_JSON(priorityClaimBag)['priorityClaim'][0]['filingDate'])) > 2018

I get the error:

No matching signature for function DATE for argument types: JSON. Supported signatures: DATE(TIMESTAMP, [STRING]); DATE(DATETIME); DATE(INT64, INT64, INT64); DATE(DATE); DATE(STRING) at [28:23]

This is in fact a string field with a valid datetime. Using the alias does not seem to work either.

Is it infact possible to evaluate this string as JSON, parse the object and then cast to DATE in this way?

CodePudding user response：

Use JSON_EXTRACT_SCALAR instead as in below example

WHERE 
  EXTRACT(YEAR FROM DATE(JSON_EXTRACT_SCALAR(priorityClaimBag, '$.priorityClaim[0].filingDate'))) > 2018