# ARRAY The `ARRAY` data type in SingleStore Helios Procedural SQL (PSQL) represents 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/array/#UUID-98007870-e0a2-cb59-850f-b50e059ebd8b.md). ## 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl.md) or [DML](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml.md) statements. An array element can be assigned to a variable, and the variable can be used in a DDL or DML statement. See [CREATE\_ARRAY](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-array.md) for information on how to assign an array element to a variable. Arrays can be defined with an unlimited number of dimensions. See the [Multidimensional Arrays section](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/array.md) 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: ```sql 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: ```sql myarray ARRAY(INT) DEFAULT [1, 2, 3]; ``` ```sql myarray ARRAY(INT) = [1, 2, 3]; ``` The following example demonstrates how to initialize an array using the `CREATE_ARRAY()` function: ```sql myarray ARRAY(INT) = CREATE_ARRAY(3); ``` The following examples show how to declare and initialize arrays of other types: ```sql 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: ```sql DELIMITER // CREATE FUNCTION get_length() RETURNS INT AS DECLARE a ARRAY(INT); BEGIN a = [1,2,3]; RETURN LENGTH(a); END // DELIMITER ; ``` ```sql SELECT get_length(); ``` ```output +--------------+ | 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: ```sql a[i] ``` For example, consider the following declared array: ```sql my_array ARRAY(VARCHAR(255)) = ["the", "quick", "brown", "fox"]; ``` The last element in array `my_array` can be accessed using the following expression: ```sql 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: ```sql a[i] = ; ``` For example, to assign `dog` to element 3 in `my_array`, use the following assignment statement: ```sql my_array[3] = "dog"; ``` Now the value of each element in `my_array` becomes: ```sql ["the", "quick", "brown", "dog"] ``` ## Multidimensional Arrays An `ARRAY` can be declared with multiple dimensions. For example, the following syntax declares a multidimensional array: ```sql my_array ARRAY(ARRAY(VARCHAR(255)); ``` The following example declares a multidimensional array with an initial value: ```sql 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: ```sql 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: ```sql 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: ```sql my_array[1][1] = "dog"; ``` The value of each element in `my_array` becomes: ```sql [["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: ```sql 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: ```sql my_array[0] = replacement; ``` You can try this out yourself using the complete example shown below: ```sql 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: ```sql SELECT md_replace_element(); ``` ```output +-------------------------------------+ | 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: ```sql my_array = [[1, 2], [1, 2, 3], [1, 2, 3, 4]]; ``` ## Convert an ARRAY to JSON The [TO\_JSON](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/to-json.md) function can be used to convert a PSQL ARRAY to a JSON value as shown in examples in the `TO_JSON` page. ## Examples The following example function creates a multidimensional array and sums the contents of its elements: ```sql 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 ; ``` ```sql SELECT md_array_sum(); ``` ```output +----------------+ | md_array_sum() | +----------------+ | 200 | +----------------+ 1 row in set (0.00 sec) ``` Here is an example of how to use `QUERY` data type with `ARRAY`: ```sql CREATE TABLE users (id INT, name VARCHAR(20)); CREATE TABLE name_stats (name VARCHAR(20), cnt INT); ``` ```sql DELIMITER // CREATE OR REPLACE PROCEDURE test_proc() AS DECLARE qry QUERY(name TEXT, num_users INT) = SELECT name, COUNT(*) FROM users; arr ARRAY(RECORD(name TEXT, num_users INT)); BEGIN arr = COLLECT(qry); FOR x in arr LOOP INSERT INTO name_stats SELECT x.name, x.num_users; END LOOP; END // DELIMITER ; ``` > **📝 Note**: The names of the fields in the `ARRAY` need to be identical to the fields in `QUERY`. ## 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. | ## Related Topics * [JSON\_AGG](https://docs.singlestore.com/db/v9.1/reference/sql-reference/json-functions/json-agg/#section-idm4605787636892833501997993222.md) *** Modified at: June 12, 2026 Source: [/db/v9.1/reference/sql-reference/procedural-sql-reference/array/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/array/) (An index of the documentation is available at /llms.txt)