google-bigquery - Not able to parse date information in Google BigQuery
问题描述
I have moved some data from MongoDB to BigQuery. Unfortunately the MongoDB source is not accessible now. I am able parse most of the information with help of stackoverflow community, Alessandro helped me out however I am having trouble with parsing date information. Following is sample data I have created
with cte as (
select 12829 as user_id,
'[{"_id":{"$oid":"5d650676af82eb0a30737e74"},"_type":"UserName","capture_date":{"$date":"2019-08-27T00:00:00.000+0000"},"source":"google","name1":"John","name3":"Doe","name4":"D/O Jane Doe","gender":"1","dob":{"$date":"1986-07-10T00:00:00.000+0000"}}]' as json_line
)
select
user_id,
json_value(json_line, '$[0].name1') as name1,
json_value(json_line, '$[0].capture_date') as capture_date,
PARSE_DATE('{"$date":"%Y-%m-%dT00:00:00.000+0000"}', json_value(json_line, '$[0].capture_date')) as capture_date1
from cte
I have already tried using parse_date, it wasn't useful. Could you please help me with my fault in provided approach or if there is any better approach to solve the same problem?
I am trying to parse cature_date information and I am getting null values.
解决方案
with cte as (
select 12829 as user_id,
'[{"_id":{"$oid":"5d650676af82eb0a30737e74"},"_type":"UserName","capture_date":{"$date":"2019-08-27T00:00:00.000+0000"},"source":"google","name1":"John","name3":"Doe","name4":"D/O Jane Doe","gender":"1","dob":{"$date":"1986-07-10T00:00:00.000+0000"}}]' as json_line
)
select
user_id,
json_extract(json_line, '$[0].name1') as name1,
json_extract_scalar(json_line, "$[0].capture_date['$date']") as capture_date,
PARSE_DATE("%Y-%m-%dT00:00:00.000+0000", json_extract_scalar(json_line, "$[0].capture_date['$date']")) as capture_date1,
from cte
或json_value
:
with cte as (
select 12829 as user_id,
'[{"_id":{"$oid":"5d650676af82eb0a30737e74"},"_type":"UserName","capture_date":{"$date":"2019-08-27T00:00:00.000+0000"},"source":"google","name1":"John","name3":"Doe","name4":"D/O Jane Doe","gender":"1","dob":{"$date":"1986-07-10T00:00:00.000+0000"}}]' as json_line
)
select
user_id,
json_extract(json_line, '$[0].name1') as name1,
json_value(json_line, '$[0].capture_date."$date"') as capture_date,
PARSE_DATE("%Y-%m-%dT00:00:00.000+0000", json_value(json_line, '$[0].capture_date."$date"')) as capture_date1,
from cte
推荐阅读
- javascript - Javascript大写功能不起作用
- swift - 实现(通用)协议的弱引用
- html - 使用 Bootstrap 在图像旁边添加文本
- reactjs - 反应无限滚动调用加载更多重复
- eigen - Java Hipparchus Eigenvector和C++ Eigen Eigenvector有不同的结果
- node.js - 使用 NodeJS Telegram Bot 输入数据 && 我无法在电报中打开浏览器并登录
- php - 如何结合两个 Ajax 和 PHP 代码进行延迟加载和按类别过滤
- android - Exoplayer 通知未清除
- kubernetes - 将域指向 Kubernetes 入口控制器 IP 时出现“503 服务暂时不可用”错误
- vue.js - vuetify 和 @mdi/js 缺少 V-Checkbox 图标。导入它的最佳方法是什么?