SingleStore (MemSQL) Procedural SQL (MPSQL) supports the ARRAY data type, which is a collection of elements that share the same data type. Arrays can be declared in the body of a user-defined function (UDF) or stored procedure (SP), and can be used in assignment statements or accessed in expressions. In addition, arrays can be specified as an input parameter or return type for UDFs or SPs.

Syntax

The following syntax declares a new array.

variable_name ARRAY(data_type [data_type_modifier]) [data_type_modifier] 
                { ; | initialization_clause }

Arguments

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 the Array Initialization section.

Remarks

Arrays use a zero-based index, where the first element in the array is at index 0. Accordingly, the last element in an array has an index equal to the array’s length minus one. For example, if an array has been declared with the following values: myarray = ["quick","brown","fox"], then the first element is "quick" at index 0, and the last element is "fox" at index 2.

An exception will be thrown if you attempt to reference an element outside the bounds of an array.

Array elements cannot be used in DDL or DML statements. An array element can be assigned to a variable, and the variable can be used in a DDL or DML statement. See how to assign an array element to a variable in the CREATE_ARRAY reference topic.

Arrays can be defined with an unlimited number of dimensions. See the Multidimensional Arrays section for more information.

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]];

Example

The following example function creates a multidimensional array and sums the contents of its elements:

DELIMITER //
CREATE OR REPLACE FUNCTION md_array_sum() RETURNS INT AS
DECLARE
  my_array ARRAY(ARRAY(INT));
  sum INT = 0;
BEGIN
  my_array = [[0, 100], [100, 0]];
  FOR i IN 0 .. LENGTH(my_array) - 1 LOOP
    FOR j IN 0 .. LENGTH(my_array[i]) - 1 LOOP
        sum += my_array[i][j];
    END LOOP;
  END LOOP;
  RETURN sum;
END //
DELIMITER ;

SELECT md_array_sum();
+----------------+
| md_array_sum() |
+----------------+
|            200 |
+----------------+
1 row in set (0.00 sec)

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:

Data Type(s)	Default Value
Integer numbers, including BOOL, BIT, TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT.	0
DOUBLE, FLOAT, and DECIMAL	0
DATE	0000-00-00
TIME	00:00:00
TIMESTAMP	0000-00-00 00:00:00
TIMESTAMP(6)	0000-00-00 00:00:00.000000
DATETIME	0000-00-00 00:00:00
DATETIME(6)	0000-00-00 00:00:00.000000
YEAR	0000
Variable-length strings, including VARCHAR(n), VARBINARY(n), LONGTEXT(n), LONGBLOB(n), MEDIUMBLOB(n), BLOB(n), TINYBLOB(n), MEDIUMTEXT(n), TEXT(n), and TINYTEXT(n).	Empty string
CHAR	One ASCII space (0x20).
CHAR(n)	One or more ASCII spaces (0x20) depending on the length.
BINARY(n)	One or more ASCII zeroes (0x00) depending on the length.
JSON	{}
GEOGRAPHY	POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))
GEOGRAPHYPOINT	POINT(0 0)
RECORD	Each field within the record will be assigned their own default values depending on their data type.