Multidimensional Array in CockroachDB

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

Introduction

In this instructional article, we will learn how to use both the Multidimensional Array in CockroachDB and Python Multidimensional List, in both Cockroach SQL and Python, respectively. We will investigate multidimensional arrays from a few points of view, including:

  • What is the overview and and syntax for using the Array data type?
  • In what circumstances and how do we use Arrays in our CockroachDB SQL? There are many ways to use this powerful feature. We will start out by sharing the workings of a single dimension array and then once we get that, we will learn to create and use 2-dimensional arrays in Cockroach.

What is an array?

An array is essentially a group or list of items. In the database and programming world, arrays can save work and increase our efficiency. You can call “0, 2, 9, 23, 5, 16” an array of integers or numeric items. You can call “Python, Cockroach, Integer, String, SQL, Query” an array of strings or array of text items. “Items” is often used to describe an individual in the group that is an array. That’s the general concept. Now let’s examine how Cockroach and Python multidimensional arrays differ.

Python List

Python’s List is a data type that has multidimensional capability. That may sound daunting. We will take it one step at a time. First, we will create a very simple one-dimensional array:

1
arrayDogs = ["Dauchshund", "Border Collie", "Poodle"]

Next we will retrieve data from the array we just created:

1
2
txtDogBreed = arrayDogs[1]
print(txtDogBreed)

“txtDogBreed” equals “Border Collie” because array counting start outs at zero. So “1” means the second item in the Python list.

Let’s loop through our array so you can see all the values:

1
2
3
arrayDogs = ["Dauchshund", "Border Collie", "Poodle"]
for txtDogBreed in arrayDogs
    print(txtDogBreed)

Here are the results of the loop you see above:

1
2
3
Dauchshund
Border Collie
Poodle

We can also add/remove items from a Python list using the append and pop methods, respectively.

Now that you have an adequater understanding of how Python lists (arrays) function, let’s learn how to use a list or array with more than one dimension. Let’s say we want to track breeds (like we were) AND colors.

1
2
3
arrayDogs = [["Dauchshund", "Border Collie", "Poodle"], ["Black", "Red", "White"]]
for i in range(len(arrayDogs))
    print("My " + arrayDogs[0][i] + " is " + arrayDogs[1][i])

Note: Python’s “len” function gives us the size of the array in number of items.

The results:

1
2
3
My Dauchshund is Black
My Border Collie is Red
My Poodle is White

Here’s how it happens:

1
2
arrayStuffs = [["arr 0 n 0", "arr 0 n 1", "arr 0 n 2"], ["arr 1 n 0", "arr 1 n 1", "arr 1 n 2"]]
print(arrayStuffs[0,1]) # gives us "arr 0 n 1"

NOTE about Python Arrays and Lists: For ease-sake, we’ve been treating them here as if they are the same thing. Mostly, they are. But there is one significant difference to keep in mind: If you create an array in Python as we’ve shown you above, you can store multiple data types all within the same array. For example: arrayElements = [5, “Tesla”, 2.3]. Notice how we mixed an integer-type number with a string and a real number? If you use “import array as arr” and then use arrayElements = arr.array(‘i’, [5, “Ford”, 2.3]), you will get an error. Note: The “i” you see in that array creation stands for “integer”. So yeah, you have to set the type of the entire array when using the array module instead of lists.

Now that we get how to create arrays / lists in Python, let’s examine how they work in the world of CockroachDB.

Cockroach Array

Cockroach has a type called Array. So yes, within one cell (cross section between a record and column), you can have – instead of an integer or text data type – an array data type. Is your mind blown a little bit yet? This can be a difficult concept for some, so we shall take it slow.

1
2
3
4
CREATE TABLE tblUsers (
    txtNameUser text
    , arrayIntGrade INTEGER[]
)

In the Cockroach query above, you may recognize the text data type that is being used for “txtNameUser”. The column we named “arrayIntGrade” may be new for you as it is an array data type. The brackets (“[]”) are how we tell Cockroach to “type this column as an array.” The way we named the column has no relevance to Cockroach. We named it that way because of our own needs for clarity and ease. You may want to find and read our article on naming conventions.

We will generate some data from the table filled with some test data so we can get a visual on how an array works in CockroachDB.

1
2
3
4
5
SELECT
    txtNameUser text
    , arrayIntGrade INTEGER[]
FROM
    tblUsers

Gives us…

txtNameUserarrayIntGrade
Steve50, 75
Bill98, 90, 95
Sharon92, 95, 99
Todd100, 99, 105, 104
George70, 85
Sal87
Fred84, 82, 89, 88

Please note: The actual data returned from CockroachDB included some “{}” brackets that we removed from here so as to show you a less cluttered and easier to get view of the data.

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

1
2
3
4
5
SELECT
    txtNameUser text
    , arrayIntGrade INTEGER[1]
FROM
    tblUsers

Gives us:

txtNameUserarrayIntGrade
Steve50
Bill98
Sharon92
Todd100
George70
Sal87
Fred84

Analysis

The “[1]” in “arrayIntGrade integer[1]” told the SQL interpretation engine to give us the first item in the “arrayIntGrade” column where that column is of the array data type.

Using arrays in the WHERE clause

Beginning with our original table:

txtNameUserarrayIntGrade
Steve50, 75
Bill98, 90, 95
Sharon92, 95, 99
Todd100, 99, 105, 104
George70, 85
Sal87
Fred84, 82, 89, 88
1
2
3
4
5
6
7
SELECT
    txtNameUser text
    , arrayIntGrade INTEGER[1]
FROM
    tblUsers
WHERE
    arrayIntGrade IN (89, 92, 98, 100)

In the above Cockroach SQL, let’s study the WHERE / IN clause above. It tells CockroachDB to “Return only records where one of the numbers in the arrayIntGrade field is in the list that includes 89, 92, 98, and 100.”

That SQL will return:

txtNameUserarrayIntGrade
Bill98
Sharon92
Todd100
Fred89

Notice a valuable difference between Python lists and Cockroach arrays is that the index for items in Python start outs with “0” while in CockroachDB arrays it start outs with “1”.

Now that you get the basics of single-dimensional arrays, it’s time to go a little deeper; one more dimension deeper, to be exact.

Cockroach Multidimensional Array

Just like we saw with Python, Cockroach also offers multidimensional arrays. Looking back at our first example, we tracked the grades each user has accumulated in tests they’ve taken. What if, for each grade per user, we want to also track the year the test was taken in? That’s where a second dimension comes in handy.

1
2
3
4
5
6
DROP TABLE tblUsers;

CREATE TABLE tblUsers (
    txtNameUser text
    , arrayIntGrade INTEGER[][]
)

Notice the similarity of this Cockroach use of arrays with creating lists in Python?

Now we have recreated the test table, but with one difference. arrayIntGrade integer[] has changed to arrayIntGrade integer[][]. Adding the second set of brackets tells Cockroach that our data type is still an array but now our Array of integers has two dimensions. As we did before, we will examine this data visually, so we can see the two dimensions in action.

Filled with data, our table looks like…

txtNameUserarrayIntGrade
Steve{2019, 50}, {2019, 75}
Bill{2018, 98}, {2018, 90}, {2019, 95}
Sharon{2017, 92}, {2018, 95}, {2019, 99}
Todd{2018, 99}, {2019, 105}, {2019, 104}
George{2019, 70}, {2019, 85}
Sal{2019, 87}
Fred{2018, 82}, {2018, 89}, {2019, 88}

Note: Again, we’ve removed the extra curly brackets that might make the lesson more confusing.

Studying the data above, we can glean the following:

  • Steve scored 50 on a test in 2019 and then 75 on a test in 2019.
  • Bill scored 98 on a test in 2018 and then 90 on another test in 2018. He then scored 95 on a test in 2019.
  • Sharon scored 92 in 2017, 95 in 2018, and then 99 in 2019.
  • Todd scored 99 in 2018, 105 in 2019, and then 104 in his next test in 2019.
  • George scored a 70 on his first 2019 test and a 85 on his second.

Conclusion

In this instructional article, your knowledge grew about how to use both Cockroach and Python multidimensional array and created some examples together to aid in easy learning. We saw that Python offers two kinds of arrays, one with strong typing called “array” and a more flexible one called “list”, which is – for obvious reasons – far more popular. And we focused here mainly on using lists, as they are more closely related to CockroachDB’s array type.

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.