How to get all json array attribute values ​​using bigquery jsonpath in bigquery? Asterisk is not supported.

Question

I am trying to get all the values ​​of a specific attribute from a json array. Given the following json, I am trying to get all 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 use $.phoneNumbers[*].type , which works fine on online parsers but when I use it in a large request:

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

I get:

 JSONPath parse error at: [*].type 

Answer 1

You can write Javascript UDF to do the extraction:

 SELECT JSON_EXTRACT('[1,2,3]', '$[*]') parsed Error: Unsupported operator in JSONPath: * 

Alternative 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 

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 )

Answer 2

json_extract cannot return a REPEATED field, it can only perform one match - hence, support for *

Answer 3

Another interesting (I hope) solution for BigQuery Standard SQL
It can be easily adjusted to suit any specific needs.

 #standardSQL CREATE TEMPORARY FUNCTION parseJson(data STRING) RETURNS ARRAY<STRUCT<parent STRING, item STRING, key STRING, value STRING>> LANGUAGE js AS """ x = JSON.parse(data); z = []; processKey(x, ''); function processKey(node, parent) { if (parent !== '') {parent += '.'}; Object.keys(node).map(function(key) { value = node[key].toString(); if (!value.startsWith('[object Object]')) { var q = {}; var arr = parent.split('.'); q.parent = arr[0]; q.item = arr[1]; q.key = key; q.value = value; z.push(q); } else { processKey(node[key], parent + key); }; }); }; return z; """; WITH t AS ( SELECT """ { "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"}, { "type" : "work", "number": "0123-4567-7777"}] } """ AS info ) SELECT parent, item, key, value FROM t, UNNEST(parseJson(info)) WHERE parent = 'phoneNumbers' AND key = 'type'