ARRAY

variable_name

The name of the array variable.

data_type

Any scalar-valued or non-scalar valued data type, except for QUERY types. For a complete list of data types, see the Data Types topic.

data_type_modifier

The optional modifier for the data type, such as NULL or NOT NULL. There are two kinds of optional modifiers in an array declaration: the modifier that applies to the array type itself, and the modifier that applies to each element in the array. By default, an array type is NOT NULL.

initialization_clause

An optional clause that initializes the array. For more information, see Array Declaration and Initialization.

Array Declaration and Initialization

Array types are declared in a function or procedure as either an input parameter or as a variable in the DECLARE block. For example:

CREATE FUNCTION my_function(arr1 ARRAY(INT)) AS
  DECLARE
    -- Declares a new uninitialized array
    arr2 ARRAY(VARCHAR(255)); 
    -- Declares a new array initialized with specific values
    arr3 ARRAY(INT) = [1, 2, 3]; 
...

You can initialize an array in the DECLARE block of a function or procedure using an assignment, either by using the DEFAULT keyword or the = or := assignment operators.

The following examples demonstrate how to initialize an array:

myarray ARRAY(INT) DEFAULT [1, 2, 3];

myarray ARRAY(INT) = [1, 2, 3];

The following example demonstrates how to initialize an array using the CREATE_ARRAY() function:

myarray ARRAY(INT) = CREATE_ARRAY(3);

The following examples show how to declare and initialize arrays of other types:

my_varchar_array ARRAY(VARCHAR(255)) = ["one", "two", "three"];
my_double_array ARRAY(DOUBLE) = [1.1, 2.2, 3.3];
my_json_array ARRAY(JSON) = ['{"number": 1}', '{"number": 2}', '{"number": 3}'];
my_multi_array ARRAY(ARRAY(INT)) = [[1, 2, 3], [4, 5, 6]];

A declared array can also be initialized using an assignment statement in the BEGIN ... END block of a function or procedure. The following example returns the size of an initialized array:

DELIMITER //
CREATE FUNCTION get_length() RETURNS INT AS
  DECLARE
    a ARRAY(INT);
  BEGIN
    a = [1,2,3];
    RETURN LENGTH(a);
  END //
DELIMITER ;

SELECT get_length();

+--------------+
| get_length() |
+--------------+
|            3 |
+--------------+
1 row in set (0.10 sec)

Array Access and Assignment

To access the value of element i in array a, use the following notation:

a[i]

For example, consider the following declared array:

my_array ARRAY(VARCHAR(255)) = ["the", "quick", "brown", "fox"];

The last element in array my_array can be accessed using the following expression:

my_array[3]; 

The expression above evaluates to fox.

Array assignment is performed in a similar way. To assign a value to element i of array a, use an assignment statement such as:

a[i] = <value>;

For example, to assign dog to element 3 in my_array, use the following assignment statement:

my_array[3] = "dog";

Now the value of each element in my_array becomes:

["the", "quick", "brown", "dog"]

Multidimensional Arrays

An ARRAY can be declared with multiple dimensions. For example, the following syntax declares a multidimensional array:

my_array ARRAY(ARRAY(VARCHAR(255));

The following example declares a multidimensional array with an initial value:

my_array ARRAY(ARRAY(VARCHAR(255))) = [["the", "quick"], ["brown", "fox"]];

Multidimensional Array Access and Assignment

Multidimensional arrays are assigned and accessed using syntax similar to that used with a one-dimensional array, but by using another set of square brackets for each additional dimension. To access the value of element i in array 0 of multidimensional array md, use the following notation:

md[0][i]

Given a multidimensional array with values [["the", "quick"], ["brown", "fox"]], you can access the value of the last element in the first array using the following syntax:

element_value VARCHAR(255) = my_array[0][1];

The value of element_value becomes quick.

To assign the value of the last element in the last array:

my_array[1][1] = "dog";

The value of each element in my_array becomes:

[["the", "quick"], ["brown", "dog"]]

You can replace an array-type element in a multidimensional array with another initialized array that uses the same element data type. For example, consider the following two array variables:

my_array ARRAY(ARRAY(VARCHAR(255))) = [["the", "quick"], ["brown", "fox"]];
replacement ARRAY(VARCHAR(255)) = ["the", "lazy"];

To replace the first array in my_array with replacement, i.e. replacing ["the", "quick"] with ["the", "lazy"], simply set the element value as shown below:

my_array[0] = replacement;

You can try this out yourself using the complete example shown below:

DELIMITER //
CREATE OR REPLACE FUNCTION md_replace_element() RETURNS VARCHAR(255) AS
  DECLARE
    my_array ARRAY(ARRAY(VARCHAR(255))) = [["the", "quick"], ["brown", "fox"]];
    replacement ARRAY(VARCHAR(255)) = ["the", "lazy"];
    result VARCHAR(255);
  BEGIN
    my_array[0] = replacement;
    result = array_as_string(my_array);
    RETURN result;
  END //

CREATE OR REPLACE FUNCTION array_as_string(a ARRAY(ARRAY(VARCHAR(255))))
RETURNS VARCHAR(255) AS
  DECLARE
    result VARCHAR(255) = "Values: [";
  BEGIN
    FOR i IN 0 .. LENGTH(a) - 1 LOOP
      result = CONCAT(result, "[");
      FOR j IN 0 .. LENGTH(a[i]) - 1 LOOP
        IF j < LENGTH(a[i]) - 1 THEN
          result = CONCAT(result, a[i][j], ", ");
        ELSE
          result = CONCAT(result, a[i][j]);
        END IF;
      END LOOP;
      IF i < LENGTH(a) - 1 THEN
        result = CONCAT(result, "], ");
      ELSE
        result = CONCAT(result, "]");
      END IF;
    END LOOP;
    RETURN CONCAT(result, "]");
  END //
DELIMITER ;

This example returns the following result:

SELECT md_replace_element();

+-------------------------------------+
| md_replace_element()                |
+-------------------------------------+
| Values: [[the, lazy], [brown, fox]] |
+-------------------------------------+
1 row in set (1.07 sec)

Lastly, each array in a multidimensional array can be of a different length. For example:

my_array = [[1, 2], [1, 2, 3], [1, 2, 3, 4]];

Array Default Values

When an array is created with the CREATE_ARRAY() function, each element in the array will be set to a default value. If the type is nullable, the default value will be NULL. For types declared with the NOT NULL modifier, each element in the newly created array is initialized with a default value that depends on the element data type for the array. The default values for each type are specified in the table below: