如何在bigquery中使用jsonpath bigquery获取json数组属性的所有值?不支持星号运算符. [英] How to get all values of an attribute of json array with jsonpath bigquery in bigquery? Asterisk operator not supported.

问题描述

我正在尝试从 json 数组中获取某个属性的所有值.考虑到以下 json，我正在尝试获取所有类型，例如iPhone，家

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"
     }
 ]
}

我正在使用 $.phoneNumbers[*].type，它似乎在 在线解析器但是当我在大查询中使用它时:

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

我明白了:

JSONPath parse error at: [*].type

推荐答案

你可以写一个Javascript UDF来做提取:

You can write a Javascript UDF to do the extraction:

SELECT JSON_EXTRACT('[1,2,3]', '$[*]') parsed 
Error: Unsupported operator in 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]') parsed 

更复杂的例子:

#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 

(灵感来自:https://discuss.httparchive.org/t/javascript-library-detection/955)

这篇关于如何在bigquery中使用jsonpath bigquery获取json数组属性的所有值?不支持星号运算符.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！