Cloud

array

In Redpanda SQL, an array stores a collection of elements that have the same data type (any built-in data type can be used).

Currently, the implementation is limited only to single-dimensional arrays.

Array type declaration

An array type can be declared by appending square brackets to the data type of its elements:

CREATE TABLE movie_night (
    event_date DATE NOT NULL,
    movies_planned TEXT[5] NOT NULL
);

This syntax specifies the size of the array. However, it does not enforce any limits, and the behavior is the same as for arrays of unspecified length. There is also another way to declare an array, by prepending the array keyword after the data type of the elements:

CREATE TABLE movie_night (
    event_date DATE NOT NULL,
    movies_planned TEXT ARRAY NOT NULL
);

Array values

You can create array literals by using the array keyword and combining it with the array’s values enclosed in square brackets and separated by commas:

ARRAY[ value1 , value2 , ... ]

You can use such a literal with, for example, SELECT or INSERT INTO statements:

SELECT ARRAY['10:14:25'::time, '22:58:11'::time];
      ?column?
---------------------
 {10:14:25,22:58:11}
(1 row)

INSERT INTO movie_night VALUES
('2024-12-01', ARRAY['Inception', 'Interstellar', 'The Prestige']);
INSERT 0 1

SELECT * FROM movie_night;
 event_date |             movies_planned
------------+-----------------------------------------
 2024-12-01 | {Inception,Interstellar,"The Prestige"}
(1 row)

You can also use a string representation of an array as another available option for array’s values syntax. It requires the elements' values to be enclosed in curly braces and separated by commas:

'{ value1 , value2 , ... }'

You can use such an array value representation in, for example, INSERT INTO statements with the VALUES clause:

INSERT INTO movie_night VALUES ('2024-12-15', '{The Matrix, John Wick}');
INSERT 0 1

SELECT * FROM movie_night;
event_date | movies_planned
------------+-----------------------------------------
2024-12-01 | {Inception,Interstellar,"The Prestige"}
2024-12-15 | {"The Matrix","John Wick"}
(2 rows)

Any element can be enclosed in double quotes and this is required, if the value contains commas or curly braces:

SELECT '{"{\"key1\": 1, \"key2\": \"value\"}", NULL, true}'::json[];
                   ?column?
-----------------------------------------------
 {"{\"key1\":1,\"key2\":\"value\"}",NULL,true}
(1 row)

In this example, the double quotes which are a part of the json value are required to be escaped with a backslash, so that they are not mistaken with the double quote, which marks the end of the element.

Access arrays

You can retrieve a single element from an array using the array subscript operator. When it comes to array values indexing, the elements of an n-length array start at index 1 and end at index n:

SELECT movies_planned,
       movies_planned[1] AS first_movie,
       movies_planned[3] AS third_movie
FROM   movie_night;
             movies_planned              | first_movie | third_movie
-----------------------------------------+-------------+--------------
 {Inception,Interstellar,"The Prestige"} | Inception   | The Prestige
 {"The Matrix","John Wick"}              | The Matrix  |
(2 rows)

If the index exceeds the length of an array, the returned value is NULL.

Arrays can also be accessed by using array slices. An array slice is denoted by writing lower_bound:upper_bound. The bounds can be omitted, in which case the slice is unbounded from a given side:

SELECT movies_planned[:]   as "unbounded slice",
       movies_planned[1:2] AS "[1:2] slice",
       movies_planned[2:]  AS "[2:] slice"
FROM   movie_night;
             unbounded slice             |        [1:2] slice         |          [2:] slice
-----------------------------------------+----------------------------+-------------------------------
 {Inception,Interstellar,"The Prestige"} | {Inception,Interstellar}   | {Interstellar,"The Prestige"}
 {"The Matrix","John Wick"}              | {"The Matrix","John Wick"} | {"John Wick"}
(2 rows)

Limitations

Field size limit

In Redpanda SQL, the field size limit for variable-size types is 32MB and this limit applies to arrays as well. If a value exceeds the given limit, an error is returned:

CREATE TABLE tb (array_column bigint[]);
CREATE

COPY tb FROM '/.oxla/long_array_value.csv';
ERROR:  Error in row 1, column array_column value exceeds size of 33554432

Unsupported SQL clauses

Array columns cannot be used as the key columns in ORDER BY, GROUP BY, or JOIN operations. You also cannot use array columns as part of the index of a table. For these operations, Redpanda SQL returns an error message:

SELECT * FROM movie_night ORDER BY movies_planned;
ERROR:  could not identify an ordering operator for type text[]

Arrays can still be used in ORDER BY or JOIN operations, if the array column is not the key:

SELECT * FROM movie_night ORDER BY event_date ASC;
 event_date |             movies_planned
------------+-----------------------------------------
 2024-12-01 | {Inception,Interstellar,"The Prestige"}
 2024-12-15 | {"The Matrix","John Wick"}
(2 rows)

Unsupported SQL statements

Specific SQL statements currently do not support arrays. These include:

  • INSERT INTO with SELECT: Arrays cannot be directly imported using an INSERT INTO with a SELECT statement. Instead, use the COPY FROM CSV command or the INSERT INTO statement with the VALUES keyword

  • UPDATE and DELETE: Updating or deleting records from a table, which contains array columns is not supported

  • COPY TO: Exporting data from array columns using the COPY TO command is not available.

  • CREATE INDEX: Index on a table cannot be created on an array column.

Any effort to use such operations with arrays will result in an error. For now, these limitations should be considered when designing tables that include array columns.