Updating nested repeated fields in BigQuery

[healthjeanie]

My team is facing a challenge in creating and updating a table that has nested repeated elements.

The table PHP has an element called php_goals which is of type RECORD and mode REPEATED. And php_goals further contains an element named goal_activities which also is of type RECORD and mode REPEATED.

This basically gives the following schema:

When we tried to add a new goal, we keep getting an error: Array element type STRUCT<STRING, STRING.....>> does no coerce to STRUCT<goal_id, goal_name.....>> at [20:7]

We are writing a query based on the BigQuery documentation - however every reference to this on the web has tables of depth 1, ours is 2.

INSERT INTO php (php_id,
php_name,
php_goals) values ("1","Johnny",
    ARRAY<
      STRUCT<
        goal_id STRING,
        goal_name STRING,
        ARRAY<
          STRUCT<
            activiy_id STRING, 
            activity_name STRING>>>>
      [("current","123 First Avenue", [("activity_id_1","activity_name_1")])

In my opinion this is fairly trivial should work, but it doesn't. WHY and how can we fix this?