Google BigQuery解析JSON日期字符串转DATE报错，如何解决？

问题描述 #

我正在解析Google BigQuery表patents-public-data.uspto_peds.backfile，表中字段为JSON格式字符串。在SELECT语句中可以通过以下方式提取单个值：

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

但尝试在WHERE子句中筛选日期晚于2018年的记录时，使用以下语句报错：

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

错误信息：

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]

实际filingDate是包含有效日期时间的字符串字段，使用别名也无法解决问题，想知道能否通过解析JSON并转为DATE进行筛选？

解决方案 #

问题出在PARSE_JSON返回的是JSON类型，而DATE()函数不接受JSON类型作为参数。你需要先把JSON中的filingDate值转换为字符串类型，再传入DATE()函数。

可以用两种方式实现：

1. 使用CAST()函数显式转换：

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

2. 使用BigQuery的类型转换语法::STRING：

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

如果filingDate的格式是标准的日期字符串（比如YYYY-MM-DD），上述两种方式都能正常将其转为DATE类型，进而提取年份进行筛选。

内容的提问来源于stack exchange，提问作者I'm Root James