json - 在 Bigquery json_extract() 函数中转义字符

Question

当使用 Google 的 BigQuery 时，有一个 function可以使用 jsonPath 从 json 字符串中提取元素。例如:

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

当 json 键本身包含一个点时，{"key.value":"value"} 目前尚不清楚如何正确转义它。

这个 jsonpath message board question说jsonpath本身就支持这种格式

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

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

但是在 bigquery 中，这种类型的转义尝试会导致 Error: JSONPath parse error 错误。

Answer 1

更新，新答案:

BigQuery 的 JSON_EXTRACT 和 JSON_EXTRACT_SCALAR 函数现在支持 JSONPath 中的 JSON 括号表示法，因此以下查询有效:

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

返回

{"foo":"bar"}

旧的，现在已经过时的答案:

不幸的是，BigQuery 不支持转义 json 路径中的特殊字符。解决方法是使用 REPLACE 函数将点转换为下划线，即

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