CODEWITHSUNDEEP
jsongoogle-bigquery
Ereli
Ereli

Reputation: 1014

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,{"key.value":"value"} It's not clear how to escape that properly.

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

@Test 
public void path_with_bracket_notation() throws Exception { 
    String json = "{\"foo.bar\": {\"key\": \"value\"}}"; 

    Assert.assertEquals("value", JsonPath.read(json, "$.['foo.bar'].key"));

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

Upvotes: 3

Views: 9530

Answers (2)

Lan Si
Lan Si

Reputation: 100

use backtick to escape (it is also used to escape hyphen in project/dataset name)

SELECT JSON_VALUE(json_field.`key.value`) AS feature FROM tablename

Upvotes: 1

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

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

{"foo":"bar"}

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.

SELECT 
  json_extract(
    replace('{"key.value":"value"}',
    'key.value',
    'key_value'),
  '$.key_value')

Upvotes: 4

Related Questions