ARRAY - CockroachDB

The ARRAY data type stores one-dimensional, 1-indexed, homogeneous arrays of any non-array . The ARRAY data type is useful for ensuring compatibility with ORMs and other tools. However, if such compatibility is not a concern, it’s more flexible to design your schema with normalized tables. CockroachDB supports indexing array columns with . This permits accelerating containment queries ( and ) and overlap queries () on array columns by adding an index to them.

CockroachDB does not support nested arrays.

Syntax

A value of data type ARRAY can be expressed in the following ways:

Appending square brackets ([]) to any non-array .
Adding the term ARRAY to any non-array .

Size

The size of an ARRAY value is variable, but it’s recommended to keep values under 1 MB to ensure performance. Above that threshold, and other considerations may cause significant performance degradation.

Functions

For the list of supported ARRAY functions, see .

Examples

Creating an array column by appending square brackets

> CREATE TABLE a (b STRING[]);

> INSERT INTO a VALUES (ARRAY['sky', 'road', 'car']);

> SELECT * FROM a;

        b
------------------
  {sky,road,car}
(1 row)

Creating an array column by adding the term `ARRAY`

> CREATE TABLE c (d INT ARRAY);

> INSERT INTO c VALUES (ARRAY[10,20,30]);

> SELECT * FROM c;

      d
--------------
  {10,20,30}
(1 row)

Accessing an array element using array index

Arrays in CockroachDB are 1-indexed.

> SELECT * FROM c;

      d
--------------
  {10,20,30}
(1 row)

> SELECT d[2] FROM c;

  d
------
  20
(1 row)

Accessing an array column using containment queries

You can use the <@ (“is contained by”) and @> (“contains”) to run containment queries on ARRAY columns.

> SELECT * FROM c WHERE d <@ ARRAY[10,20,30,40,50];

      d
--------------
  {10,20,30}
(1 row)

> SELECT * FROM c WHERE d @> ARRAY[10,20];

      d
--------------
  {10,20,30}
(1 row)

Using the overlaps operator

You can use the && (overlaps) to select array columns by checking if another array overlaps the column array. Arrays overlap if they have any elements in common.

Create the table:
CREATE TABLE a (b STRING[]);

Insert two new arrays:

INSERT INTO a VALUES (ARRAY['runway', 'houses', 'city', 'clouds']);
INSERT INTO a VALUES (ARRAY['runway', 'houses', 'city']);
INSERT INTO a VALUES (ARRAY['sun','moon']);

Use the && operator in a WHERE clause to a query:

SELECT * FROM a WHERE b && ARRAY['clouds','moon'];

                  b
-------------------------------
  {runway,houses,city,clouds}
  {sun,moon}
(2 rows)

Time: 30ms total (execution 2ms / network 28ms)

Appending an element to an array

Using the `array_append` function

> SELECT * FROM c;

      d
--------------
  {10,20,30}
(1 row)

> UPDATE c SET d = array_append(d, 40) WHERE d[3] = 30;

> SELECT * FROM c;

        d
-----------------
  {10,20,30,40}
(1 row)

Using the append (`||`) operator

> SELECT * FROM c;

        d
-----------------
  {10,20,30,40}
(1 row)

> UPDATE c SET d = d || 50 WHERE d[4] = 40;

> SELECT * FROM c;

         d
--------------------
  {10,20,30,40,50}
(1 row)

Ordering by an array

> CREATE TABLE t (a INT ARRAY, b STRING);

> INSERT INTO t VALUES (ARRAY[3,4],'threefour'),(ARRAY[1,2],'onetwo');

> SELECT * FROM t;

    a   |     b
--------+------------
  {3,4} | threefour
  {1,2} | onetwo
(2 rows)

> SELECT * FROM t ORDER BY a;

    a   |     b
--------+------------
  {1,2} | onetwo
  {3,4} | threefour
(2 rows)

Supported casting and conversion

between ARRAY values is supported when the data types of the arrays support casting. For example, it is possible to cast from a BOOL array to an INT array but not from a BOOL array to a TIMESTAMP array:

> SELECT ARRAY[true,false,true]::INT[];

   array
-----------
  {1,0,1}
(1 row)

> SELECT ARRAY[true,false,true]::TIMESTAMP[];

pq: invalid cast: bool[] -> TIMESTAMP[]

You can cast an array to a STRING value, for compatibility with PostgreSQL:

> SELECT ARRAY[1,NULL,3]::string;

    array
--------------
  {1,NULL,3}
(1 row)

> SELECT ARRAY[(1,'a b'),(2,'c"d')]::string;

               array
------------------------------------
  {"(1,\"a b\")","(2,\"c\"\"d\")"}
(1 row)

Implicit casting

CockroachDB supports implicit casting from string literals to arrays of all data types except the following:

Box2D
GEOGRAPHY
GEOMETRY

For example, if you create a table with a column of type INT[]:

> CREATE TABLE x (a UUID DEFAULT gen_random_uuid() PRIMARY KEY, b INT[]);

And then insert a string containing a comma-delimited set of integers contained in brackets:

> INSERT INTO x(b) VALUES ('{1,2,3}'), (ARRAY[4,5,6]);

CockroachDB implicitly casts the string literal as an INT[]:

> SELECT * FROM x;

                   a                   |    b
---------------------------------------+----------
  2ec0ed91-8a82-4f2e-888e-ae86ece4fc60 | {4,5,6}
  a521d6e9-3a2a-490d-968c-1365cace038a | {1,2,3}
(2 rows)

​Syntax

​Size

​Functions

​Examples

​Creating an array column by appending square brackets

​Creating an array column by adding the term ARRAY

​Accessing an array element using array index

​Accessing an array column using containment queries

​Using the overlaps operator

​Appending an element to an array

​Using the array_append function

​Using the append (||) operator

​Ordering by an array

​Supported casting and conversion

​Implicit casting

​See also

Syntax

Size

Functions

Examples

Creating an array column by appending square brackets

Creating an array column by adding the term `ARRAY`

Accessing an array element using array index

Accessing an array column using containment queries

Using the overlaps operator

Appending an element to an array

Using the `array_append` function

Using the append (`||`) operator

Ordering by an array

Supported casting and conversion

Implicit casting

See also