How to get all values of an attribute of json array with jsonpath bigquery in bigquery? Asterisk operator not supported.

Question

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

Answer 1

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)