BigQuery - JSONpath recursive operator (1/2)

Question

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

Motivation: access "name" without knowing "students" and "class" in the below.

Query

SELECT JSON_EXTRACT(json_text, '$..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

Try below

SELECT REGEXP_EXTRACT(json_text, r'"name" : "(\w+)"') AS first_student
FROM UNNEST([
'{"class" : {"students" : {"name" : "Jane"}}}'
]) AS json_text;           

with output