I was wondering if BigQuery has any additional support to JSON paths, as it seems like this is such a common way to work with nested data in BigQuery. For example, as a few years ago it seemed like the answer was: What JsonPath expressions are supported in BigQuery?, i.e., "Use a UDF".
However, it seems like using a path within an array, such as:
`$..Job'
Is such a common operation given BigQuery's repeated field, that about 70% of the times I've tried to use BigQuery's JSON_EXTRACT, I run into the limitation of having to iterate down an array.
Is this ability supported yet in BigQuery, or are there plans to support it, without having to do a UDF? As nice as something like the following works:
CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS STRING
LANGUAGE js AS """
try { var parsed = JSON.parse(json);
return JSON.stringify(jsonPath(parsed, json_path));
} catch (e) { return null }
"""
OPTIONS (
library="gs://xx-bq/jsonpath-0.8.0.js"
);
SELECT CUSTOM_JSON_EXTRACT(to_json_string(Occupation), '$..Job'), to_json_string(MovieInfo), json_extract(MovieInfo, '$.Platform') FROM `xx-163219.bqtesting.xx` LIMIT 1000
It ends up taking anywhere between 4-6x longer than a normal JSON_EXTRACT function (2s vs. about 10s). Or, is there something that I'm missing with what you're able to do with JSON objects in BQ?
