how do i extract json from Google BigQuery using a function when the field contains @ sign BigQuery Standard SQL

Question

so my table looks like this

{"@timestamp":"2018-08-08T09:21:57.947+00:00","@version":"1","message":"bla bla"}

i can extract for example the message part using the json functions like below

JSON_EXTRACT_SCALAR(log,'$.message') AS message

but when i try to extract the timestamp the same way

JSON_EXTRACT_SCALAR(log,'$.@timestamp') AS timestamp

i get the error "Error: Unsupported operator in JSONPath: @" any ideas on the correct syntax?

Answer 1

In cases where a JSON key uses invalid JSONPath characters, you can escape those characters using single quotes and brackets, [' ']. For example:

$['@timestamp']

SELECT 
 json_extract_scalar('{"@timestamp":"2018-08-08T09:21:57.947+00:00"}',"$['@timestamp']")

See more examples on: JSON Functions in Standard SQL