Not able to parse date information in Google BigQuery

Question

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.

user_13473525 · Answer

Try json_extract_scalar:

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

or 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

Not able to parse date information in Google BigQuery

1 Answers