如何在BigQuery中正确解析JSON字符串内的datetime数据

传入的JSON字符串不符合标准JSON语法规范：JSON中除数值、布尔值、null类型外，其余字符串类型的值必须用双引号包裹。当前示例里application_date对应的日期时间值2020-08-03 12:55:21没有加双引号，解析器默认尝试将冒号后的内容解析为数值，读到2020后遇到后续的-不属于合法数值字符，判定数值内容终止，因此最终只返回2020。

方案1：修正源JSON格式（优先推荐） #

构造JSON时给datetime类型的值加上双引号，使其成为合法的JSON字符串，再使用json_extract_scalar提取即可得到完整值，示例代码：

select json_extract_scalar(json_data, '$.application_date') as application_date
from (
  select '{"user_id":"10000561","application_date":"2020-08-03 12:55:21"}' as json_data
)

如果需要得到BigQuery原生的DATETIME类型值方便后续日期计算，可以在提取后加一层类型转换：

select datetime(json_extract_scalar(json_data, '$.application_date')) as application_date
from (
  select '{"user_id":"10000561","application_date":"2020-08-03 12:55:21"}' as json_data
)

方案2：无法修改源JSON时的兼容方案 #

如果上游生成的JSON固定为不带引号的错误格式、无法调整源数据，可以跳过JSON解析逻辑，用正则直接提取对应字段的值：

select regexp_extract(json_data, r'"application_date":([0-9\-: ]+)') as application_date
from (
  select '{"user_id":"10000561","application_date":2020-08-03 12:55:21}' as json_data
)

注意：该兼容方案仅适用于日期时间格式固定、JSON结构无大幅变动的场景，正则匹配存在失效风险，优先选择修正JSON格式的方案。

内容的提问来源于stack exchange，提问作者DreamingFighter