Array in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this lesson document, we will learn how to use Array in CockroachDB SQL. We’ll explore the array data type from more than one perspective, including:

  • What? What is the description of and syntax for using the Cockroach Array data type?
  • Why? In what situations and how do we use an Array in CockroachDB queries?
  • Additional Since ARRAY is a data type, there are a multitude of ways to use it. Here we will explore a few realistic ways and even learn how to go past single dimension arrays into multi dimensional arrays in CockroachDB.

How CockroachDB Array works

An array is a set, group, or list of items. In many languages, arrays are known as “lists”. In the database and programming world, arrays can save a large amount of work, increasing our efficiency in development and runtime. You can call “2, 4, 6, 8, 10, 12” an array of integers or numeric items. You can call “Biff, Tad, Mercedez, Marybeth, Lacrutia” an array of strings or array of text items. “Items” is often used to describe each of the individuals in the group that is a Cockroach array.

Cockroach calls the data type “Array”. So yes, within one cell (cross section between a row and column), you can actually have – instead of an integer or text data type – an array data type in CockroachDB. This can be a complex concept for some to understand, so let us take it slow.

CockroachDB Array syntax

1
2
3
4
5
CREATE TABLE tblDatabaseUsers
(
txtEmployee text
, arrayintGrade INTEGER[]
)

Analysis

In the Cockroach SQL above, you may recognize the text data type that is being used for “txtEmployee”. The column we named “arrayintGrade” is probably new for you as it is an array data type. The brackets (“[]”) are how we tell Cockroach “type this column as an array.”

Cockroach Array in Select

Now we will examine that table filled with some data so we can get a visual perspective on the concept of how Arrays work in CockroachDB.

1
2
3
4
5
SELECT
    txtEmployee text
    , arrayintGrade INTEGER[]
FROM
    tblDatabaseUsers

Returns:

txtEmployeearrayintGrade
Stevie50, 75
Billy98, 90, 95
Sharen92, 95, 99
Tadd100, 99, 105, 104
Georgie70, 85
Sally80, 87
Freddie84, 82, 89, 88

Note: The actual data returned from CockroachDB included “{}” brackets that we removed from here so as to give you a less cluttered and easier to understand look at the data.

Now we will get data from the Cockroach tblDatabaseUsers table, pulling only part of the array, so you can get an even deeper perspective on how an array works in Cockroach.

1
2
3
4
5
SELECT
    txtEmployee text
    , arrayintGrade INTEGER[2]
FROM
    tblDatabaseUsers

Returns:

txtEmployeearrayintGrade
Stevie75
Billy90
Sharen95
Tadd105
Georgie85
Sally87
Freddie89

Analysis

The [2] in arrayintGrade integer[2] told the CockroachDB SQL engine to return the second item in the “arrayintGrade” column and that column is an array data type.

Cockroach Array in WHERE

Starting with our original table:

txtEmployeearrayintGrade
Stevie50, 75
Billy98, 90, 95
Sharen92, 95, 99
Tadd100, 99, 105, 104
Georgie70, 85
Sally80, 87
Freddie84, 82, 89, 88
1
2
3
4
5
6
7
SELECT
    txtEmployee text
    , arrayintGrade INTEGER[3]
FROM
    tblDatabaseUsers
WHERE
    arrayintGrade IN (98, 92, 100)

In the above Cockroach SQL, let’s now examine the WHERE clause. We’ve told Cockroach, “Only return rows where one of the numbers in the arrayintGrade column is in the list that includes 98, 92, and 100.”

That SQL will return:

txtEmployeearrayintGrade
Billy95
Sharen99
Tadd104

Now that you understand the basics of 1-dimensional arrays, it’s time to go deeper.

Cockroach multidimensional array

There is more than one dimension to an array. Looking back at our first Cockroach array example, we were tracking the grades each user has accumulated in tests they’ve taken. What if, for each grade, we also want to also track the year it was taken in? That’s where a second dimension comes in.

1
2
3
4
5
6
DROP TABLE tblDatabaseUsers;

CREATE TABLE tblDatabaseUsers (
    txtEmployee text
    , arrayintGrade INTEGER[][]
)

We’ve recreated our Cockroach test table (tblDatabaseUsers) but with one significant difference. arrayintGrade integer[] has changed to arrayintGrade integer[][]. Adding the 2nd set of brackets tells Cockroach SQL that our data type is still an array but now, instead of a single dimension, our Array has two dimensions. As we did before, we will look at this data visually, so we can see the two dimensions in action.

Filled with data, our table looks like…

txtEmployeearrayintGrade
Stevie{2019, 50}, {2019, 75}
Billie{2018, 98}, {2018, 90}, {2019, 95}
Sharen{2017, 92}, {2018, 95}, {2019, 99}
Tad{2018, 99}, {2019, 98}, {2019, 99}
Georgio{2019, 80}, {2019, 85}
Sally{2019, 87}
Freddie{2018, 92}, {2018, 89}, {2019, 88}

Note: We’ve removed some extra curly brackets that appear in database output, which would only serve to add confusion to the lesson.

Studying the data above, we can learn the following:

  • Stevie scored 50 on a test in 2019 and then 65 on a test in 2019.
  • Billie scored 98 on a test in 2018 and then 90 on another test in 2018. He then scored 95 on a test in 2019.
  • Sharen scored 92 in 2017, 95 in 2018, and 99 in 2019.
  • Tad scored 99 in 2018, 98 in 2019, and then 99 in his next test in 2019.
  • Georgio scored a 80 on his first 2019 test and 85 on his second. Are you beginning to see how it works?

Miscellaneous

Are you curious as to why we named some columns and variables with a prefix of “tbl”, “array”, “int”, or “txt”? It’s called “naming conventions”. The naming convention you see here is the most popular amongst programmers in both database and non-database coding worlds.

Conclusion

In this lesson, we learned how and why to use the ARRAY data type in Cockroach SQL. We also used the SELECT statement and WHERE clause. There is much whole a person can accomplish with arrays as they can be seen as a mini database on their own. What we’ve learned in this lesson is just the tip of the iceberg. Look for future documents that go into other ways to use arrays, as well as using all the various functions in CockroachDB provides for array manipulation.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

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.