在Bigquery json_extract()函数中转义字符 [英] Escaping Characters in Bigquery json_extract() function
问题描述
使用 Google的BigQuery 时,有函数,它可以使用jsonPath从json字符串中提取元素。例如:
$ b $ pre $ SELECT JSON_EXTRACT(data,$ .key.value)AS特性FROM表名
当json键本身包含一个点时, {key.value:value}
目前尚不清楚如何正确转义。
此jsonpath 留言板问题表示jsonpath本身支持这种格式
@Test
public void path_with_bracket_notation()抛出异常{
String json ={\foo.bar\:{\key \:\value \ };
Assert.assertEquals(value,JsonPath.read(json,$。['foo.bar']。key));
然而,在bigquery中,这种类型的espcaping尝试导致错误:JSONPath解析错误
错误。
更新,新答案:
b$ b
BigQuery的 JSON_EXTRACT
和 JSON_EXTRACT_SCALAR
函数现在支持JSONPath中的JSON括号表示法,以下查询的工作原理如下:
SELECT JSON_EXTRACT('{key.value:{foo:bar}} ',$ ['key.value'])
并返回
{foo:bar}
旧的,现在已过时的答案: 不幸的是,BigQuery不支持在json路径中转义特殊字符。解决方法是使用REPLACE函数将点转换为下划线,即 when using Google's BigQuery, there's a function that can extract elements from json strings using jsonPath. For example: when the json key itself contains a dot, this jsonpath message board question says that jsonpath itself supports this format However in bigquery this type of espcaping attempts cause Update, new answer: BigQuery's 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.
这篇关于在Bigquery json_extract()函数中转义字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
SELECT
json_extract(
替换('{key.value:value}',
'key.value',
'key_value'),
'$ .key_value')
SELECT JSON_EXTRACT(data,"$.key.value") AS feature FROM tablename
{"key.value":"value"}
It's not clear how to escape that properly.@Test
public void path_with_bracket_notation() throws Exception {
String json = "{\"foo.bar\": {\"key\": \"value\"}}";
Assert.assertEquals("value", JsonPath.read(json, "$.['foo.bar'].key"));
Error: JSONPath parse error
errors.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']")
{"foo":"bar"}
SELECT
json_extract(
replace('{"key.value":"value"}',
'key.value',
'key_value'),
'$.key_value')