BigQuery如何使用JSON_EXTRACT获取键名带$符号的字段值

报错原因 #

JSONPath 语法规定，包含$等特殊字符的键名不能直接通过.访问符调用，必须使用方括号包裹带引号的键名才能被正确解析。你原先的查询语句中$date键名包含特殊符号$，直接写在.后不符合语法规范，因此触发了无效token报错。

修正后的查询语句 #

SELECT JSON_EXTRACT(customers, '$.created["$date"]') AS Created_date
FROM `pre-prod-286801.carli_mongo.customers`

如果需要直接提取数值格式的时间戳，避免返回结果带引号，可使用JSON_VALUE方法（适用于BigQuery环境）：

SELECT JSON_VALUE(customers, '$.created["$date"]') AS Created_date
FROM `pre-prod-286801.carli_mongo.customers`

拓展（时间戳格式转换） #

如果需要把提取到的13位毫秒时间戳转换为可读的时间格式，可添加转换逻辑：

SELECT TIMESTAMP_MILLIS(CAST(JSON_VALUE(customers, '$.created["$date"]') AS INT64)) AS Created_datetime
FROM `pre-prod-286801.carli_mongo.customers`

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