BigQuery嵌套记录INSERT语句执行失败求助：请求排查问题原因

Hey there! Let's break down what's causing your INSERT statement to fail and how to fix it.

Key Problems in Your Original SQL #

• Unnecessary alias in VALUES clause: You added as name after 'my_name'—this isn't needed here. The INSERT INTO clause already defines the column order, so the VALUES list just needs to pass the values in that exact sequence without extra aliases.
• Potential type/structure mismatch (double-check): Make sure your address column in test_table is defined as ARRAY<STRUCT<line1 STRING, line2 STRING, code INT64>>. If the table's struct fields have different names (e.g., line_1 instead of line1) or data types (e.g., code as a string), you'll still get errors even after fixing the syntax.

Corrected INSERT Statement #

INSERT INTO `my_project.my_dataset.test_table`(name, address, comments)
VALUES(
  'my_name',
  [STRUCT('ABC' AS line1, 'XYZ' AS line2, 10 AS code), STRUCT('PQR' AS line1, 'STU' AS line2, 20 AS code)],
  'Comment'
);

Quick Explanation #

• We removed the redundant as name from the first value—this was causing a syntax error because BigQuery doesn't expect column aliases inside the VALUES list.
• The struct array part is correctly formatted, but double-verify it matches your table's schema exactly. For example, if your table's address struct uses zip_code instead of code, you'll need to adjust the STRUCT definition to match that field name.

内容的提问来源于stack exchange，提问作者Dinesh