Array Cleanup

Concepts covered: Array Operations

Listing 102 counts 3 amenities but offers 2, and the NULL slots on 103 and 104 turn every search on those rows into NULL. Both defects trace back to collection: a host taps the same checkbox twice, and an unused slot is stored as NULL. Cleaning the list once means every function after it works on real, unique values. The examples here name the uncleaned column raw_amenities.

array_distinct removes repeats

array_distinct(raw_amenities) keeps the first occurrence of each value and drops later copies, so the survivors stay in their original order. NULL counts as one more value: a single NULL survives, and an array holding 3 NULLs keeps 1. cardinality counts every element, NULLs included, which is why listing 103 still reports 3 after deduplication.

WITH raw AS (
  SELECT
    ARRAY[amenity_1, amenity_2, amenity_3] AS raw_amenities,
    listing_id
  FROM listing_amenities
)
SELECT
  listing_id,
  cardinality(raw_amenities) AS raw_count,
  array_distinct(raw_amenities) AS deduped,
  cardinality(array_distinct(raw_amenities)) AS deduped_count
FROM raw

Removing NULLs: the call that backfires

The tempting call is array_remove(raw_amenities, NULL), and it returns NULL for every row: array_remove hands back NULL whenever its second argument is NULL, before it looks at the array. Even with that rule lifted, it removes elements equal to the argument, and NULL = NULL is never true.

The call that works is array_except(raw_amenities, ARRAY[CAST(NULL AS VARCHAR)]). array_except(x, y) returns the elements of x that are absent from y, and as a set operation it lets a NULL in x match the NULL in y. The CAST gives the 1-element array the VARCHAR type the amenities have.

WITH raw AS (
  SELECT
    ARRAY[amenity_1, amenity_2, amenity_3] AS raw_amenities,
    listing_id
  FROM listing_amenities
)
SELECT
  listing_id,
  array_remove(raw_amenities, NULL) AS removed,
  array_except(raw_amenities, ARRAY[CAST(NULL AS VARCHAR)]) AS excepted
FROM raw

Listing 102 in the excepted column reads [parking, wifi]. Set functions always return distinct elements, so array_except dropped the second wifi along with the NULLs. For amenities that is the goal, since a listing either has wifi or lacks it. For sequences where repeats carry information, such as the pages a guest viewed in order, the same call erases data; a later lesson covers filtering that keeps repeats.

The set functions, all deduplicating

• array_union(x, y): every distinct element found in either array.
• array_intersect(x, y): the distinct elements present in both arrays.
• array_except(x, y): the distinct elements of x that y does not hold.

Clean once, then search

With NULLs gone, contains sees every element and returns false on a miss. The CTE below cleans in one place, and the no-wifi filter, written here as a comparison with false, now returns listings 103 and 104 with no COALESCE.

WITH clean AS (
  SELECT
    array_except(ARRAY[amenity_1, amenity_2, amenity_3], ARRAY[CAST(NULL AS VARCHAR)]) AS amenities,
    listing_id
  FROM listing_amenities
)
SELECT
  listing_id,
  amenities
FROM clean
WHERE contains(amenities, 'wifi') = false

The gap between raw and clean sizes doubles as a data-quality measure. Summed across listings, it counts the junk slots a cleanup step removed, a figure worth tracking as new hosts arrive.

WITH raw AS (
  SELECT
    ARRAY[amenity_1, amenity_2, amenity_3] AS raw_amenities,
    listing_id
  FROM listing_amenities
)
SELECT
  COUNT(*) AS listings,
  SUM(cardinality(raw_amenities) - cardinality(array_except(raw_amenities, ARRAY[CAST(NULL AS VARCHAR)]))) AS junk_slots
FROM raw

The 3 junk slots are the second wifi on listing 102 and the NULL slots on 103 and 104. The raw arrays hold 15 elements and the cleaned ones 12, so 1 slot in 5 was junk. A jump in that ratio after a release points at the form that collects amenities.

Do

• Clean arrays in one CTE and point every later step at the clean column
• Drop NULLs from set-like lists with array_except and a 1-element NULL array
• Compare cardinality before and after cleanup to measure how much changed

Avoid

• Don't pass NULL to array_remove; it returns a NULL array
• Don't deduplicate sequences where repeats carry meaning, like page views
• Don't search arrays with contains while NULL slots remain in them

Listing 107's raw array is [pool, NULL, pool, NULL]. What do the 2 cleanup columns return for it?

SELECT
  array_distinct(raw_amenities) AS deduped,
  array_except(raw_amenities, ARRAY[CAST(NULL AS VARCHAR)]) AS cleaned
FROM raw
WHERE listing_id = 107

• [pool] and [pool], because both functions drop NULL elements as they go
• [pool, NULL] and [pool, pool], because except keeps repeats and drops NULLs
• [pool, NULL] and [pool], because distinct keeps one NULL and except drops it
• NULL and NULL, because a NULL element makes both calls return a NULL array

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.