How do I use BigQuery DML to insert a row with an array within a struct within an array?

I’m trying to create a Stack Overflow question where, in order to ask the question, I need to produce a minimal example. As I create the minimal example, I use BigQuery DDL to create a table. The table create step succeeds, inferring that the schema I chose is fine. But the INSERT INTO statement I wrote to insert a row into it causes an error about nested arrays not being allowed.

The code for creating the table, which succeeds:

CREATE TABLE `my_dataset.test_data_for_so`
(
  date DATE,
  hits ARRAY<STRUCT<search STRUCT<query STRING, other_column STRING>, metadata ARRAY<STRUCT<key STRING, value STRING>>>>
);

This is what the schema looks like in the BigQuery GUI after I create the table:

But when I try to use INSERT INTO to insert a row of data into this table with the following BigQuery DML code:

INSERT INTO `my_dataset.test_data_for_so` (date, hits)
VALUES (CAST('2021-01-01' AS date), [STRUCT<query STRING, other_column STRING>('foo bar', 'foo bar'), [STRUCT<key STRING, value STRING>('foo bar', 'foo bar')]])

I get an error:

Cannot construct array with element type ARRAY<STRUCT<key STRING, value STRING>> because nested arrays are not supported at [8:103]`.

BigQuery’s documentation suggests that this should be possible. They state that arrays within arrays are not possible, but that if you use a struct in between, it’s possible. And this is what I believe I’m doing:

BigQuery does not support building arrays of arrays directly. Instead, you must create an array of structs, with each struct containing a field of type ARRAY.

https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#building_arrays_of_arrays

Answer

Use below instead – you were missing one level of STRUCT

INSERT INTO `my_dataset.test_data_for_so` (date, hits)
VALUES (
    CAST('2021-01-01' AS date), 
    [STRUCT(
        STRUCT<query STRING, other_column STRING>('foo bar', 'foo bar') , 
        [STRUCT<key STRING, value STRING>('foo bar', 'foo bar')]
    )] 
    );

Now it is inserted into table

Advertisement

Answer