Training Outcomes Within Your Budget!

We ensure quality, budget-alignment, and timely delivery by our expert instructors.

Share this Resource

Table of Contents

SQL Array: Explained in Detail

SQL Arrays allow for managing multiple values in a single database column, providing a structured method for handling intricate data sets. SQL Arrays in tables allow for efficient operations such as grouping related items and managing multi-value attributes. It also streamlines specific query types by directly storing arrays.  

Nevertheless, although arrays can improve Data Management, they also bring difficulties, such as possible performance problems and challenges with indexing. In addition, SQL Databases differ in their support for array data types, with specific databases like PostgreSQL providing strong array capabilities. Others, like MySQL, rely on alternative methods like JSON. In this blog, let’s see why SQL Arrays are crucial for designing and optimising databases efficiently and how to create them.

Table of Contents   

 1) What is an Array?  

 2) How do you Create an SQL Array?  

 3) SQL Array Functions: What do They do?  

      a) ARRAY_AGG  

      b) ARRAY_APPEND  

      c) ARRAY_CONCAT  

      d) ARRAY_JOIN  

      e) Array_Length  

      f) ARRAY_PREPEND  

      g) ARRAY_CONTAINS  

      h) ELEMENT_AT  

 4) Conclusion 

What is an Array?   

What is an Array

An Array is a database comprising a structure where data is stored corresponding to indexes. The position of an element in an Array is relative to the index. You can determine the position of a data element in an Array simply by finding the offset and adding it to a base value.  


Microsoft SQL Server Training
 

The offset is the difference between two indexes, and the base value, for the most part, is index 0. While there isn’t a native function to define an Array, different organisations have come up with their own functions to do so. An SQL Array is an ordered set of elements that has all elements of the same built-in data type.

Learn to manage Databases effectively with our Microsoft SQL Server Training - Register now! 

How do You Create an SQL Array? 

An important aspect to consider is that ‘ARRAY’ or ‘array’ isn’t a native function in most DBMSs. This presents the challenge of defining an array using non-native functions in certain DBMSs. A notable and straightforward example of this can be found in PostgreSQL. Oracle also supports array data types by creating nested tables, which adds complexity.  

The creation of nested tables is quite complex and often cumbersome. Nesting inherently brings additional complexity, making PostgreSQL a better choice for illustrating this concept. 

Structured Query Language (SQL) is a standard language for managing and manipulating databases. Understanding SQL is crucial for effectively working with databases, including handling arrays and nested tables in different DBMSs.

Here’s an example that’ll explain how to create an SQL Array column for grades: 

For this one, let’s take into account fields such as “ID,” “Name,” “Age,” and “Grades” for a table named “Students.” 
 

CREATE TABLE Students ( 
ID SERIAL PRIMARY KEY, 
Name VARCHAR (50), 
Age INTEGER, 
Grades INTEGER [] 
); 


Now, an Array can be deployed to store data from the “Grades” field. You can insert data into all the fields in this process as well. Here’s what it’ll look like: 
 

INSERT INTO Students (Name, Age, Grades) 
VALUES (‘Student A’, 18, ‘{80, 91, 75}’); 
 
INSERT INTO Students (Name, Age, Grades) 
VALUES (‘Student B’, 19, ‘{88, 100, 99}’); 


If you wish to retrieve data from the table, all you have to do is run a SELECT query like this: 
 

SELECT * FROM Students; 


And this is what the output will look like: 
 

ID |   Name    | Age | Grades 
---------------------------------- 
1 | Student A | 18 | {80, 91, 75} 
2 | Student B | 19 | {88,100,99}  

 

It’s that simple! However, the aforementioned disclaimer shouldn’t be forgotten. Arrays aren’t native to SQL, so you might have to consider the possibility of adapting multiple functions that do the same job. 

Learn to analyse data with our Introduction to SQL Databases Training 10985C – Sign up now!

SQL Array Functions: What do They do? 

While using SQL Array, you’ll come across a list of functions that’ll help you perform multiple actions. Once again, the functions listed below have been chosen based on their commonality across multiple DBMSs since different DBMSs can use different functions. Please note that the syntaxes demonstrated in the examples are based on PostgreSQL DBMS.   

1) ARRAY_AGG 

The ARRAY_AGG function enables the creation of Arrays from columns of a result set. In simpler terms, it allows you to create Arrays to handle multiple groups of data by specifying cohorts. Here’s an example with a syntax that’ll demonstrate the function’s usage: 

Suppose you have a table named “Employees” that contains three fields, “Name,” “ID,” and “Department” that looks like this: 
 

ID |     Name      | Department 
--------------------------------- 
1 | Employee A | Finance 
2 | Employee B | HR 
3 | Employee C | IT 
4 | Employee D | IT 
5 | Employee E | HR   


Now, if you want to perform taxonomy on employees based on their departments, here’s how you can do so with the ‘ARRAY_AGG’ function:
 

SELECT Department, ARRAY_AGG(Name) AS employee_names 
FROM Employees 
GROUP BY Department; 


Once done, you’ll get the following result:  

 

Department | employee_names 
------------------------------------------------------ 
HR         | {Employee B, Employee E} 
Finance    | {Employee A} 
IT         | {Employee C, Employee D} 

 

2) ARRAY_APPEND 

The ARRAY_APPEND function allows you to append values to an existing Array. Bear in mind that the elements you want to add should be of the same data type as the ones already existing in the Array. Here’s how you can add an element using the ARRAY_APPEND function:    

Disclaimer: Unfortunately, there’s no standard ARRAY_APPEND function in PostgreSQL. You’ll have to create a custom function named ARRAY_APPEND to get the job done. 
 

-- Create a custom function ARRAY_APPEND 
CREATE OR REPLACE FUNCTION ARRAY_APPEND(arr anyarray, elem anyelem) 
RETURNS anyarray AS 
$$ 
BEGIN 
     RETURN arr || elem; 
END; 
$$ 
LANGUAGE plpgsql; 

 

Now, you can use the ARRAY_APPEND function to append elements to an Array. Suppose you want to add the number “5” to an existing Array in a table of numbers. Here’s how you can do it: 
 

CREATE TABLE Numbers ( 
ID SERIAL PRIMARY KEY, 
values INTEGER[] 
); 
INSERT INTO Numbers (values) VALUES (‘{1,2,3,4}’); 

 

Now that the table and the Array have been defined, you can proceed with the addition of the number “5.” 
 

UPDATE Numbers 
SET values = ARRAY_APPEND (values, 5) 
WHERE ID = 1; 

 

Once added, the updated Array will become ‘{1,2,3,4,5}.’ 

3) ARRAY_CONCAT 

As the name suggests, you can combine two or more Arrays to make a new one using this function. In PostgreSQL, you can perform concatenation using two different methods. One requires you to create a custom ‘ARRAY_CONCAT’ function, while the other can be done using the ‘array_cat’ function. Here’s how the first method works: 
 

-- Create a custom function ARRAY_CONCAT 
CREATE OR REPLACE FUNCTION ARRAY_CONCAT (arr1 anyarray, arr2 anyarray) 
RETURNS anyarray AS 
$$ 
BEGIN 
RETURN arr1 || arr2; 
END; 
$$ 
LANGUAGE plpgsql; 

 

Now that your custom function has been created, you can use it to do the desired operation. Suppose you have two Arrays, namely {0,1,2} and {3,4,5}. Here’s how you can perform concatenation on the same: 
 

SELECT ARRAY_CONCAT (ARRAY[0,1,2], ARRAY[3,4,5]) AS concatenated_array; 

 

The output will look something like this: 
 

concatenated_array 
--------------------------- 
{0,1,2,3,4,5} 

 

Now, for the second method, here’s how you can get the same result: 
 

SELECT ARRAY[0,1,2] AS array1, ARRAY[3,4,5] AS array2; 

Deploying ‘array_cat’ will look something like this: 

SELECT array_cat(array1, array2) AS concatenated_array FROM (SELECT ARRAY[0,1,2] AS array1, ARRAY[3,4,5] AS array2) AS subquery; 

 

The output for this method will look something like this: 
 

concatenated_array 
--------------------------- 
{0,1,2,3,4,5} 

 

Learn to troubleshoot SQL problems with our SQL Server 2014 Performance Tuning and Optimisation 55144BC Training – Sign up now!

4) ARRAY_JOIN 

The ‘ARRAY_JOIN’ function allows you to concatenate elements from an Array consisting of strings. While the function itself isn’t a standard one, you can create a custom one by defining the parameters. Also, you can use an ‘array_to_string’ function to do the same job. Here’s how you can transform an SQL Array to a string using the ‘array_to_string’ function: 

Say you have an Array of strings: 
 

SELECT ARRAY[ ‘Black’, ‘White’, ‘Orange’] AS my_array; 

It’ll look something like this: 

my_array 
----------------------------- 
{Black, White, Orange} 

Now, you can concatenate the elements using the ‘array_to_string’ function like this: 

SELECT array_to_string(my_array, ‘,’) AS concatenated_string FROM (SELECT ARRAY[‘Black’, ‘White’, ‘Orange’] AS my_array) AS subquery; 

 

Bear in mind that you’ll have to define the delimiter (“,”, “.”, “;”) to separate the elements from each other. Once done, your output will look something like this: 
 

concatenated_string 
------------------------------ 
Black, White, Orange

 

The second method involves creating a custom function, ‘ARRAY_JOIN’, here’s how you can do it: 

Let’s consider the same array as mentioned in the previous method. 
 

my_array 
----------------------------- 
{Black, White, Orange} 

 

Here’s how you can create a custom function: 
 

CREATE OR REPLACE FUNCTION ARRAY_JOIN(arr anyarray, delimiter text) 
RETURN text AS 
$$ 
BEGIN 
RETURN array_to_string(arr, delimiter); 
END; 
$$ 
LANGUAGE plpsql; 

Here’s how you can use the custom function to create a string: 

SELECT ARRAY_JOIN(my_array, ‘,’) AS concatenated_string FROM (SELECT ARRAY[‘Black’, ‘White’, ‘Orange’] AS my_array) AS subquery; 

 

The output will look something like this: 
 

concatenated_string 
------------------------------ 
Black, White, Orange

 

5) array_length 

The problem of finding the SQL Array length can be solved by using ‘array_length’. This function takes into account an Array and its dimension. A dimension here refers to the layers of an Array. If used in a one-dimensional Array, it’ll give you the number of elements in that Array. If used in a multi-dimensional Array, though, it’ll give you the length of a specific dimension. 

If you have a one-dimensional Array, here’s how you can find its length: 
 

SELECT array_length(ARRAY[5, 10 15, 20], 1) AS array_length; 

 

Where 1 is the dimension of the array. 

You’ll get: 
 

array_length 
------------------- 


4 is the number of elements in the aforementioned Array. 

If you have a multi-dimensional Array, here’s how you can find its length: 
 

SELECT array_length(ARRAY[[1,2,3], [4,5,6], [7,8,9]], 2) AS array_length; 

 

Where 2 is the dimension of the Array. 

You’ll get: 
 

array_length 
--------------------- 

 

6) ARRAY_PREPEND 

As the name suggests, the ‘ARRAY_PREPEND’ function allows you to prepend a value into the Array without modifying the original one. Here’s how it works: 

Suppose you have an Array with the elements {2,3,4}, and you wish to add 1 to the beginning. All you have to do is: 
 

SELECT ARRAY_PREPEND(1, ARRAY[2,3,4]) AS new_array; 

 

This will give you the following result: 
 

new_array 
--------------- 
{1,2,3,4} 

 

Learn to write query with our Writing Analytical Queries for Business Intelligence 55232A Training today! 

7) ARRAY_CONTAINS 

This is a unique function as it can be represented with an operator and can also be created as a custom function. The ‘ARRAY_CONTAINS’ function is used in instances where you wish to know whether an Array contains elements from another Array. The answers, for the most part, are boolean values (TRUE, FALSE). 

If you want to create a custom function named ‘ARRAY_CONTAINS, here’s what you have to do: 
 

CREATE OR REPLACE FUNCTION ARRAY_CONTAINS(arr anyarray, elem anyelement) 
RETURNS BOOLEAN AS 
$$ 
BEGIN 
RETURN elem = ANY(arr); 
END; 
$$ 
LANGUAGE plpgsql; 

 

Now, say you have an Array {2,4,6,8}, and you wish to know whether element 4 exists in the Array or not. Here’s what you have to do: Now, say you have an Array {2,4,6,8}, and you wish to know whether element 4 exists in the Array or not. Here’s what you have to do: 
 

SELECT ARRAY_CONTAINS(ARRAY[2,4,6,8], 4) AS element_exists; 

 

Here, you’ll receive the following output: 
 

element_exists 
-------------------- 
TRUE 

 

The second method involves using the ‘@>’ operator. Here’s how it works: 
 

SELECT ARRAY[2,4,6,8] AS my_array; 


The Array shall remain the same from the previous example. Upon deploying ‘@>’ this happens: 
 

SELECT ARRAY[2,4,6,8] @> ARRAY[4] AS element_exists; 

 

The output turns out to be: 
 

element_exists 
-------------------- 
TRUE 

 

8) ELEMENT_AT 

ELEMENT_AT follows the same trajectory as that of the aforementioned function. You can find the location of an element in an Array using this function. You can either create a custom ‘ELEMENT_AT’ function, or you can find an element using Array indexing. 

If you want to access an element using Array indexing, here’s what you have to do: 

Say you have an Array that looks like this, {1,3,5,7}, and you wish to find the element at position 3. Using Array indexing, here’s how you can do it: 
 

SELECT ARRAY[1,3,5,7] AS my_array; 

 

Which gives the following Array: 
 

my_array 
-------- 
{1,3,5,7} 

 

Now, here’s what Array indexing does: 
 

SELECT my_array[3] AS element_at_position_3 FROM (SELECT ARRAY[1,3,5,7] AS my_array) AS subquery; 

 

This will give you the following output: 
 

element_at_position_3 
------------------------------- 

 

You can do the same by creating a custom function like this: 
 

CREATE OR REPLACE FUNCTION ELEMENT_AT(arr anyarray, position integer) 
RETURNS anyelement AS 
$$ 
BEGIN 
RETURN arr[position]; 
END; 
$$ 
LANGUAGE plpgsql; 

 

Now that the custom function has been created, here’s how you can use it: 
 

SELECT ELEMENT_AT(ARRAY[1,3,5,7], 3) AS element_at_position_3; 

 

This will give you the following output: 
 

element_at_position_3 
------------------------------- 


Conclusion 

We hope you understand What SQL Arrays are and  what their functions are.. SQL Arrays provide a strong method for storing and handling data collections in a single column, allowing for more complex data structures and making certain tasks easier. Nevertheless, there are limitations to their usage, including potential performance drawbacks, difficulties in querying and indexing, and decreased data normalisation. Moreover, the level of support for arrays differs among various SQL Databases, impacting their ability to be easily transferred. 

Understand the SSIS Architecture with our SQL Server Integration Services 55321AC Training today!
 

Frequently Asked Questions

Which SQL Databases Support Array Data Types? faq-arrow

PostgreSQL and Oracle have built-in array data types, while MySQL and SQL Server handle similar functionality through the use of JSON or table-valued parameters.

What are the Limitations of Using Arrays in SQL? faq-arrow

Arrays within SQL Databases can make indexing and querying more complex, decreasing performance speed. They could also decrease Data standardisation, thus raising storage needs. Furthermore, some SQL Databases do not inherently support arrays, which hinders their portability.

What are the Other Resources and Offers Provided by The Knowledge Academy? faq-arrow

The Knowledge Academy takes global learning to new heights, offering over 30,000 online courses across 490+ locations in 220 countries. This expansive reach ensures accessibility and convenience for learners worldwide.

Alongside our diverse Online Course Catalogue, encompassing 17 major categories, we go the extra mile by providing a plethora of free educational Online Resources like News updates, Blogs, videos, webinars, and interview questions. Tailoring learning experiences further, professionals can maximise value with customisable Course Bundles of TKA.
 

What is The Knowledge Pass, and How Does it Work? faq-arrow

The Knowledge Academy’s Knowledge Pass, a prepaid voucher, adds another layer of flexibility, allowing course bookings over a 12-month period. Join us on a journey where education knows no bounds.

What are the Related Courses and Blogs Provided by The Knowledge Academy? faq-arrow

The Knowledge Academy offers SQL Courses, including the Introduction To SQL, Advanced SQL, and Introduction To MySQL Course. These courses cater to different skill levels, providing comprehensive insights into Basic and Advanced SQL Functions.

Our Programming & DevOps Blogs cover a range of topics related to SQL, offering valuable resources, best practices, and industry insights. Whether you are a beginner or looking to advance your Programming & DevOps skills, The Knowledge Academy's diverse courses and informative blogs have got you covered.
 

Upcoming Programming & DevOps Resources Batches & Dates

Get A Quote

WHO WILL BE FUNDING THE COURSE?

cross

OUR BIGGEST SUMMER SALE!

Special Discounts

red-starWHO WILL BE FUNDING THE COURSE?

close

close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.

close

close

Press esc to close

close close

Back to course information

Thank you for your enquiry!

One of our training experts will be in touch shortly to go overy your training requirements.

close close

Thank you for your enquiry!

One of our training experts will be in touch shortly to go over your training requirements.