如何在bigquery中使用jsonpath bigquery获取json数组属性的所有值?不支持Asterisk运算符。 [英] How to get all values of an attribute of json array with jsonpath bigquery in bigquery? Asterisk operator not supported.
问题描述
我试图从json数组中获取某个属性的所有值。
考虑下面的json,我试图获得所有类型,例如iPhone,home
{
firstName:John,
lastName:doe ,
年龄:26,
地址:
{
streetAddress:naist street,
城市:奈良,
postalCode:630-0192
},
phoneNumbers:
[
{
type:iPhone,
number:0123-4567-8888
},
{
type:home,
number:0123-4567- 8910
}
我正在使用 $。phoneNumbers [*] .type
这似乎在在线解析器
但是当我在大查询中使用它时:
选择json_extract(my_column,'$。phoneNumbers [*]。type')
from my_table
<
JSONPath解析错误:[*]。type
解决方案您可以编写一个Javascript UDF来进行提取: b
SELECT JSON_EXTRACT('[1,2,3]','$ [*]')解析
错误:JSONPath中不支持的运算符:*
UDF替代:
#standardSQL
CREATE TEMPORARY FUNCTION parseJson(libs STRING)
RETURNS ARRAY< INT64>
LANGUAGE js AS
try {
return JSON.parse(libs);
} catch(e){
return [];
}
;
SELECT parseJson('[1,2,3]')解析
更复杂的示例:
#standardSQL
CREATE TEMPORARY FUNCTION parseJson(libs STRING)
RETURNS ARRAY< STRUCT< x INT64 ,y INT64,z INT64>>
LANGUAGE js AS
try {
return JSON.parse(libs);
} catch(e){
return [];
}
;
SELECT parseJson(JSON_EXTRACT('{a:[{x:1},{y:2},{z:3}]}','$ .a'))已解析
(受启发: https://discuss.httparchive.org/t/javascript-library-detection/955 )
I'm trying to get all the values of a certain attribute from a json array. Considering the following json, I'm trying to get all the types e.g. iPhone,home
{ "firstName": "John", "lastName" : "doe", "age" : 26, "address" : { "streetAddress": "naist street", "city" : "Nara", "postalCode" : "630-0192" }, "phoneNumbers": [ { "type" : "iPhone", "number": "0123-4567-8888" }, { "type" : "home", "number": "0123-4567-8910" } ] }
I am using
$.phoneNumbers[*].type
which seems to work fine on online parsers but when I'm using it in big query:select json_extract(my_column,'$.phoneNumbers[*].type') from my_table
I get:
JSONPath parse error at: [*].type
解决方案You can write a Javascript UDF to do the extraction:
SELECT JSON_EXTRACT('[1,2,3]', '$[*]') parsed Error: Unsupported operator in JSONPath: *
UDF alternative:
#standardSQL CREATE TEMPORARY FUNCTION parseJson(libs STRING) RETURNS ARRAY<INT64> LANGUAGE js AS """ try { return JSON.parse(libs); } catch (e) { return []; } """; SELECT parseJson('[1,2,3]') parsed
More complex example:
#standardSQL CREATE TEMPORARY FUNCTION parseJson(libs STRING) RETURNS ARRAY<STRUCT<x INT64, y INT64, z INT64>> LANGUAGE js AS """ try { return JSON.parse(libs); } catch (e) { return []; } """; SELECT parseJson(JSON_EXTRACT('{"a":[{"x":1},{"y":2},{"z":3}]}', '$.a')) parsed
(inspired by: https://discuss.httparchive.org/t/javascript-library-detection/955)
这篇关于如何在bigquery中使用jsonpath bigquery获取json数组属性的所有值?不支持Asterisk运算符。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!