Does BigQuery have a safe navigation operator?

Question

Does BigQuery have a safe navagation operator, i.e. a null-safe variant of its field navigation operator?

Ideally I'm looking for an operator akin to ?. in Swift/TypeScript, &. in Ruby, etc., but a function I could call would suffice as well.

Right now my query looks like:

SELECT a.b.c.d.e
FROM myTable AS a
WHERE
    a.b IS NOT NULL
  && a.b.c IS NOT NULL
  && a.b.c.d IS NOT NULL
  && a.b.c.d.e = "my desired value"

Edit: This doesn't actually work.

Name b not found inside a at [12:34]

I'd wish it could be something like:

SELECT a.b.c.d.e
FROM myTable AS a
WHERE a?.b?.c?.d?.e = "my desired value"

Answer 1

Afaik, there is no safe navigation operator for STRUCT type in bigquery.

What I can come up with is to convert nested STRUCT type to JSON type and utilize json path with which you can navigate safely.

WITH myTable AS (
  SELECT STRUCT(STRUCT(STRUCT('my_desired_value' AS e) AS d) AS c) AS b
)
SELECT TO_JSON(b).c.d.e, --
       TO_JSON(b).f.d.e, -- non-existing path
       -- b.f.d.e  --> error - Field name f does not exist ...
  FROM myTable AS a;

To check field path of struct type, you can use INFORMATION_SCHEMA.COLUMN_FIELD_PATHS.

SELECT * 
  FROM `your-project.your_dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
 WHERE table_name = 'myTable';