Arrays in SQL

Concepts covered: Arrays

An ordered collection as a single value

An array is a SQL value that holds several elements of one type, in a fixed order, under a single column. Its type names the element type: ARRAY(VARCHAR) holds strings and ARRAY(INTEGER) holds whole numbers. Order is part of the value, so an array of Mission then SoMa differs from an array of SoMa then Mission. A delivery route is the natural case: the same 3 neighborhoods in a different order is a different drive.

You can write an array directly with the ARRAY[...] constructor, as in ARRAY['Mission', 'SoMa', 'Marina']. Most arrays in a warehouse come from data rather than literals, and the most common source is delimited text. SPLIT(text, delimiter) cuts a string at each delimiter and returns the pieces as an ARRAY(VARCHAR), in the order they appeared.

The stops column stores each batch's route as neighborhoods joined by commas. That is a string, so LENGTH(stops) counts characters and says nothing about how many stops a route has. Splitting it on the comma gives an array with one element per stop, in driving order.

SELECT
  batch_id,
  SPLIT(stops, ',')[1] AS first_stop,
  CARDINALITY(SPLIT(stops, ',')) AS stop_count
FROM batches

Batch 701 splits into 3 elements and the first is Mission. Batch 704 has no comma, so SPLIT returns an array of 1 element holding Nob Hill. The brackets after the array pick one position, and SQL arrays count positions from 1, so [1] is the first stop. A JSON path counts from 0, so the same bracket means a different thing depending on whether you are inside a path string or next to a SQL array.

Reading one position

Asking for a position the array does not have raises an error that stops the whole query: SPLIT(stops, ',')[3] fails on batch 702, which has 2 stops. ELEMENT_AT(array, position) does the same lookup but returns NULL when the position is out of range, which makes it the right choice when routes differ in length. For the last element, hand ELEMENT_AT the array's own size: CARDINALITY(array) counts the elements, so ELEMENT_AT(array, CARDINALITY(array)) lands on the last one whatever the length.

SELECT
  batch_id,
  SPLIT(stops, ',') AS stop_list,
  ELEMENT_AT(SPLIT(stops, ','), 3) AS third_stop,
  ELEMENT_AT(SPLIT(stops, ','), CARDINALITY(SPLIT(stops, ','))) AS last_stop
FROM batches

Batches 702, 704 and 705 have no third stop, and ELEMENT_AT reports NULL for each instead of halting the query. The last_stop column is right for every row regardless of length, because each row's position comes from its own element count: 3 for batch 701, 1 for batch 704.

stops[4]

• Reads the element at position 4
• Raises an error when the array has fewer than 4 elements
• Fits data where every array has a known length

ELEMENT_AT(stops, 4)

• Reads the element at position 4
• Returns NULL when the array has fewer than 4 elements
• Suits routes of different lengths, such as 1 to 4 stops

Asking about the whole array

CARDINALITY(array) returns the number of elements as a BIGINT, and CONTAINS(array, value) returns true when any element equals the value. Both return ordinary SQL types, so they slot into WHERE, GROUP BY and HAVING without any conversion.

SELECT
  CARDINALITY(SPLIT(stops, ',')) AS stop_count,
  COUNT(*) AS batches
FROM batches
GROUP BY CARDINALITY(SPLIT(stops, ','))

Two of the 5 batches run 2 stops, and the others run 1, 3 and 4. A WHERE clause can lean on CONTAINS the same way: WHERE CONTAINS(SPLIT(stops, ','), 'Marina') keeps batches 701, 703 and 705, the 3 routes that pass through the Marina, and drops the other 2.

Batch 704 stores Nob Hill in stops, with no comma anywhere. What does ELEMENT_AT(SPLIT(stops, ','), 2) return for it?

• NULL, because the array has 1 element
• Hill, the second word of the name
• Nob Hill, the only element there is
• An error, because position 2 is out of range

Characters versus elements

Two habits carried over from strings and JSON paths trip people up here. LENGTH counts characters in a string, and CARDINALITY counts elements in an array, so on a route string they answer different questions. A SQL array also has no position 0: the first element is [1], and [0] stops the query with an error saying SQL array indices start at 1. Batch 703 shows both.

The 29 counts every letter and all 3 commas in Sunset,Richmond,Haight,Marina; the 4 is the number of stops Amara drives.

About this lesson

This section is part of the Complex Data: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.