google-bigquery - 如何在bigquery中使用jsonpath bigquery获取json数组属性的所有值？不支持星号运算符。

Question

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

{
 "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，它似乎在 online parsers 上运行良好
但是当我在大查询中使用它时:

select json_extract(my_column,'$.phoneNumbers[*].type')
from my_table

我得到:

JSONPath parse error at: [*].type

Answer 1

您可以编写一个 Javascript UDF 来进行提取:

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)