Ereli Ereli - 4 months ago 30
JSON Question

Escaping Characters in Bigquery json_extract() function

when using Google's BigQuery, there's a function that can extract elements from json strings using jsonPath. For example:

SELECT JSON_EXTRACT(data,"$.key.value") AS feature FROM tablename

when the json key itself contains a dot,
It's not clear how to escape that properly.

this jsonpath message board question says that jsonpath itself supports this format

public void path_with_bracket_notation() throws Exception {
String json = "{\"\": {\"key\": \"value\"}}";

Assert.assertEquals("value",, "$.[''].key"));

However in bigquery this type of espcaping attempts cause
Error: JSONPath parse error


Update, new answer:

BigQuery's JSON_EXTRACT and JSON_EXTRACT_SCALAR functions now support JSON bracket notation in JSONPath, so the following query works:

SELECT JSON_EXTRACT('{"key.value": {"foo": "bar"}}', "$['key.value']")

and returns


Old, now outdated answer:

Unfortunatelly BigQuery does not support escaping special characters in json path. The workaround would be to use REPLACE function to convert dots to underscores, i.e.