Array Operations

Concepts covered: Array Operations

An array column holds an ordered list in each row, so a single cell can carry every amenity a listing offers. The first 2 sections run on 5 listings whose arrays come from 3 amenity slots on the listing_amenities table. Hosts fill those slots by hand, which leaves listing 102 with wifi entered twice and listings 103 and 104 with an empty slot stored as NULL. Most examples build the array once, in a CTE named listings.

Searching with contains

contains(amenities, 'wifi') asks whether a value appears anywhere in the array. A match returns true. A miss returns false only when every element is known: if the array holds a NULL, the blank slot might have been wifi, so the answer is NULL. That is three-valued logic applied to each element in turn. The animation runs the check over the raw slots, building the array inline.

SELECT
  listing_id,
  ARRAY[amenity_1, amenity_2, amenity_3] AS amenities,
  contains(ARRAY[amenity_1, amenity_2, amenity_3], 'wifi') AS has_wifi
FROM listing_amenities

Listings 103 and 104 get NULL for has_wifi, and that NULL causes trouble once the question flips to listings without wifi. NOT applied to NULL is still NULL, and WHERE keeps only rows where the condition is true. The first tab returns zero rows even though 2 listings have no wifi entry. The second tab turns the unknown answer into false before negating, so a blank slot counts as no wifi.

NOT contains

Negates contains directly. Both listings without wifi hold a NULL slot, both conditions are NULL, and the result is empty.

WITH listings AS (
  SELECT
    listing_id,
    ARRAY[amenity_1, amenity_2, amenity_3] AS amenities
  FROM listing_amenities
)
SELECT
  listing_id,
  amenities
FROM listings
WHERE NOT contains(amenities, 'wifi')

COALESCE

Replaces NULL with false before the NOT, so listings 103 and 104 pass the filter.

WITH listings AS (
  SELECT
    listing_id,
    ARRAY[amenity_1, amenity_2, amenity_3] AS amenities
  FROM listing_amenities
)
SELECT
  listing_id,
  amenities
FROM listings
WHERE NOT COALESCE(contains(amenities, 'wifi'), false)

Positions: array_position and element_at

array_position(amenities, 'hot_tub') returns the 1-based position of the first match and 0 on a miss, even when the array holds NULLs. To go the other way, from position to value, square brackets work until the position runs past the end: amenities[4] on a 3-element array stops the query with an error. element_at(amenities, 4) returns NULL there, and a negative position counts back from the end, so element_at(amenities, -1) reads the last slot.

WITH listings AS (
  SELECT
    listing_id,
    ARRAY[amenity_1, amenity_2, amenity_3] AS amenities
  FROM listing_amenities
)
SELECT
  listing_id,
  array_position(amenities, 'hot_tub') AS hot_tub_pos,
  element_at(amenities, 4) AS fourth_slot,
  element_at(amenities, -1) AS last_slot
FROM listings

Sorting with array_sort and reverse

array_sort orders the elements ascending and places every NULL at the end. The 1-argument form has no descending option, so descending order comes from reverse(array_sort(...)). Reverse flips the entire array, NULLs included, which moves them from the end to the front. Pair the sort with element_at and the NULL placement decides what the alphabetically last amenity turns out to be.

WITH listings AS (
  SELECT
    listing_id,
    ARRAY[amenity_1, amenity_2, amenity_3] AS amenities
  FROM listing_amenities
)
SELECT
  listing_id,
  array_sort(amenities) AS sorted_asc,
  reverse(array_sort(amenities)) AS sorted_desc,
  element_at(array_sort(amenities), -1) AS last_sorted
FROM listings

Listings 103 and 104 report NULL as last_sorted, because their blank slot sorted to the back. Removing NULLs before sorting, the job of the next section, makes the last element a real amenity.

Modifying: append, remove, slice

No array function changes the stored column. Each one returns a new array for that row of the result. amenities || 'smoke_alarm' appends one element, and || between 2 arrays joins them end to end, as concat(a, b) does. array_remove(amenities, 'wifi') drops every element equal to wifi, so both copies on listing 102 go. slice(amenities, 1, 2) returns 2 elements starting at position 1.

WITH listings AS (
  SELECT
    listing_id,
    ARRAY[amenity_1, amenity_2, amenity_3] AS amenities
  FROM listing_amenities
)
SELECT
  listing_id,
  amenities || 'smoke_alarm' AS appended,
  slice(amenities, 1, 2) AS first_two
FROM listings

Run on the 5 listings, array_remove leaves listing 102 with [parking] and keeps the NULL slots on 103 and 104. It drops only elements equal to wifi, and a NULL is not equal to wifi, so the blank slot stays in the result.

WITH listings AS (
  SELECT
    listing_id,
    ARRAY[amenity_1, amenity_2, amenity_3] AS amenities
  FROM listing_amenities
)
SELECT
  listing_id,
  amenities,
  array_remove(amenities, 'wifi') AS no_wifi
FROM listings

Listing 106 has amenities [tv, NULL, wifi]. What does this query return for it?

SELECT
  contains(amenities, 'pool') AS has_pool,
  array_position(amenities, 'wifi') AS wifi_pos
FROM listings
WHERE listing_id = 106

• false and 3, because pool appears nowhere in the array
• NULL and 3, because the NULL slot might be pool and still takes position 2
• NULL and 2, because the NULL slot is skipped when counting positions
• false and 2, because both functions ignore NULL elements entirely

About this lesson

This section is part of the Complex Data: Intermediate 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.