BigQuery - JSONpath recursive operator (2/2)

Question

Is there any way to realize a recursive search on a JSON string object in BigQuery in absence of the operator "..", which is apparently not supported ?

Motivation: access "name" only when located within "students" in the below.

Query

SELECT JSON_EXTRACT(json_text, '$..students.name') AS first_student
FROM UNNEST([
'{"class" : {"students" : {"name" : "Jane"}}}'
]) AS json_text;

Desired output

+-----------------+
| first_student   |
+-----------------+
| "Jane"          |
+-----------------+

Current output

Unsupported operator in JSONPath: ..

Answer 1

Is there any way to realize a recursive search on a JSON string object in BigQuery in absence of the operator "..", which is apparently not supported ?

Consider below approach

CREATE TEMPORARY FUNCTION CUSTOM_JSON_EXTRACT(json STRING, json_path STRING)
RETURNS STRING 
LANGUAGE js AS """
  return jsonPath(JSON.parse(json), json_path);
"""
OPTIONS (
  library="gs://some_bucket/jsonpath-0.8.0.js"
);
SELECT CUSTOM_JSON_EXTRACT(json_text, '$..students.name') AS first_student
FROM UNNEST([
'{"class" : {"students" : {"name" : "Jane"}}}'
]) AS json_text;     

with output

Note: to overcome current BigQuery's "limitation" for JsonPath, above solution uses UDF + external library - jsonpath-0.8.0.js that can be downloaded from https://code.google.com/archive/p/jsonpath/downloads and uploaded to Google Cloud Storage - gs://some_bucket/jsonpath-0.8.0.js