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?

[ms4446]

The core issue stems from a mismatch in how you're structuring the data you're trying to insert. Here's the breakdown:

• Schema Mismatch: Your INSERT statement is attempting to insert an array of structs where the inner struct has fields named goal_id, goal_name, and a nested array of structs with fields activiy_id and activity_name. However, your table schema expects a nested repeated field with specific names for these fields.

• Type Mismatch: Even if you correct the field names, you might encounter another issue if the types in your INSERT statement do not match the schema. For example, if activity_id should be an INTEGER instead of STRING.

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

To update an existing row's nested repeated field, you can use the UPDATE statement with ARRAY and UNNEST functions to manipulate the nested arrays:

UPDATE php
SET php_goals = ARRAY(
    SELECT AS STRUCT goal.* REPLACE (
        (
            SELECT ARRAY_AGG(STRUCT(activity_id, activity_name))
            FROM UNNEST(goal.goal_activities) AS activity
        ) AS goal_activities
    )
    FROM UNNEST(php.php_goals) AS goal
    WHERE goal.goal_id = "current"
)
WHERE php_id = "1";

Important Considerations:

1. Schema Alignment: Double-check your actual table schema to ensure the field names and types precisely match the query.
2. Data Validation: Ensure the data you're inserting is valid and formatted correctly according to your schema.