CODEWITHSUNDEEP
google-bigquery
Ananthasireesh TA
Ananthasireesh TA

Reputation: 31

How to get a value of key with $ thru JSON_EXTRACT in BigQuery

I have got a strange issue while fetching created date from below data

"_id": {"$oid": "60c5f1370eded9c18879d4f1"}, "created": {"$date": 1633434679779}, "modified": {"$date": 1623585226955}, "active": true, "plan": "prod_JewaAj8a4FchKB", "brand": "MasterCard", "last4": "5100", "expMonth": 2, "expYear": 2022, "customerID": "cus_JfCEqBSIJWPuuD", "subscriptionID": "sub_JfCEZ9k8c2jRG1", "profileID": {"$oid": "60c50f8eb6e4a91755544080"}}

when i write below Query

SELECT (JSON_EXTRACT(customers, '$.created.$date')) AS Created_date from pre-prod-286801.carli_mongo.customers am getting error as "Invalid token in JSONPath at: .$date", Please some one help me to solve this issue

Upvotes: 0

Views: 436

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10172

Try JSON_QUERY which uses double quotes to escape invalid JSONPath characters - link

select JSON_QUERY(customers, '$.created."$date"')

Example:

select JSON_QUERY('{"created": {"$date": 1633434679779}}', '$.created."$date"')

enter image description here

Upvotes: 1

Related Questions