Multidimensional array BigQuery-CodePudding

In bigQuery is possible delcare an Array of Array like this

DECLARE ar2 ARRAY DEFAULT [['A','B','C'],['G','H','I'],['N','O','P']];

This syntax return error and I didn't find the right.

Tks

CodePudding user response：

Seems that you need to work with array of arrays, if so the documentation says:

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

The page also gives a solution for that by using a array of structs. May something like:

DECLARE ar2 DEFAULT (
WITH words AS
  (SELECT ['A','B','C'] as word
   UNION ALL SELECT ['G','H','I'] as word
   UNION ALL SELECT  ['N','O','P'] as word)
SELECT ARRAY(
  SELECT STRUCT(word)
  FROM words)
  AS all_words);

CodePudding user response：

You can't have array of arrays on BigQuery. It does not support it. As per documentation about Declaring an ARRAY type shows:

ARRAY<ARRAY> (not supported) This is an invalid type declaration which is included here just in case you came looking for how to create a multi-level ARRAY. ARRAYs cannot contain ARRAYs directly. Instead see the next example.

But there are workarounds for it, like using array with structs. Please see below code:

DECLARE TEST ARRAY<STRUCT<x ARRAY<STRING>,y ARRAY<STRING>,z ARRAY<STRING>>> 
DEFAULT[(['A','B','C'],['E','F'],['W','X','Y','Z'])];

SELECT TEST;