Working with Arrays in CockroachDB

https://www.cockroachlabs.com/docs/stable/array.html#examples

Introduction

As tech progresses we naturally start working with more complex datatypes including, arrays, JSON, and full files. In this tutorial we’ll show demonstrate how to work with the ARRAY datatype that is bound to crop up in your application.

Limitations

It’s important to know some limitations when using the ARRAY datatype:

  1. Nested arrays aren’t supported in CockroachDB.
  2. You cannot create an index on an array field.
  3. You cannot order by an array field.

These limitations aren’t exclusive to CockroachDB. Limitations 2 & 3 specifically make sense as it would be trying to sort by a field that has multiple values.

  1. It’s recommended to keep the size of your arrays below 1 megabyte.

Arrays

Array Syntax

Let’s go over some examples of the syntax for working with Arrays in CockroachDB.

Create a table schema with an array field

CREATE TABLE demotable (arrayfield STRING[]);

Insert an Array into an Array field

INSERT INTO demotable (arrayfield) VALUES (ARRAY['test1', 'test2', 'test3']);

Get a Single Value in an Array Field by Index

SELECT arrayfield[2] FROM demotable;

NOTE: This will retrieve test2 as this index is NOT zero-based like most indeces in programming.

Pushing Onto An Array Field

Pushing onto an array is simple using the array_append function which takes as parameters an array and a value to push onto it. In this case we’ll use the name of the field itself to use the stored array.

UPDATE demotable SET arrayfield = array_append(arrayfield, 'test4') WHERE arrayfield[1] = 'test1';

You can verify the push by running:

> SELECT * FROM demotable;
         arrayfield          
+---------------------------+
  {test1,test2,test3,test4}  
(1 row)

Time: 668µs

A more succinct way to push onto an array is using the || operator which will append. The same command from above written with this syntax would be:

UPDATE demotable SET arrayfield = arrayfield || 'test4' WHERE arrayfield[1] = 'test1';

Casting

We can also cast an arrays values as long as the datatypes support it. You’ll need to use the ::operator in the following format. Here we are converting an array of DECIMALS to INTS.

SELECT ARRAY[1.2,2.6,3.1]::INT[];

Here we cast an array of BOOLS to INTS.

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

You’ll receive an error if the casting the datatype to the new datatype is not possible.

Conclusion

It’s important to know how to work with Arrays in your database technology and understand what is possible with them. We hope the demo examples we showed you gave you some insight into what is possible and how to work with the Array datatype in CockroachDB. If you have any questions it’s always good to try experimenting with it in Cockroach’s demo mode or if you still have trouble don’t hesitate to reach out to us.

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.