Working with Arrays in CockroachDB
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.
It’s important to know some limitations when using the ARRAY datatype:
- Nested arrays aren’t supported in CockroachDB.
- You cannot create an index on an array field.
- 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.
- It’s recommended to keep the size of your arrays below 1 megabyte.
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 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 = 'test1';
You can verify the push by running:
> SELECT * FROM demotable;
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 = 'test1';
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.
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.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started