Complex Data: Intermediate

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.

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.

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.

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.

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.

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.

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.

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.

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(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.

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.

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.

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.

A map stores key-value pairs in one cell. Each key appears once, and every value shares one type. Listing settings fit that shape: each host sets a check-in time, a pet policy and a guest limit. The examples build a settings map per listing from 3 columns of listing_settings, keeping every value as VARCHAR so times, words and numbers can live in the same map.

element_at(settings, 'pets') reads a value by key, the same function that read arrays by position. A key the map lacks returns NULL. The bracket form settings['pets'] fails the query on a missing key, so it belongs only where every row is known to carry that key.

map_concat(a, b) combines 2 maps. A key found in only one map passes through; when both hold a key, the value from the later argument wins. That rule turns argument order into a policy. Put platform defaults first and host settings second, and hosts override the defaults. Reverse them, and the defaults overwrite what hosts chose, with no error to warn you.

Two kinds of filter apply to maps. A row filter keeps rows whose map holds some entry: WHERE element_at(settings, 'pets') = 'yes'. A row without a pets key gets NULL from element_at, the comparison is NULL, and the row drops out. An entry filter keeps chosen keys and discards the rest, and one way that needs no new function is to rebuild the map from the keys you want, reading each value with element_at.

The rebuilt map always carries both keys. If a listing had no checkin entry, its guest_view would still list the key, paired with NULL, which a consumer may read differently from an absent key. To test whether a key exists, search the key array: contains(map_keys(settings), 'checkin').

map_keys(settings) and map_values(settings) return the map's contents as 2 arrays in matching order, which opens up every array function from the first 2 sections. Entry order inside a map is not something to depend on, so wrap map_keys in array_sort before displaying or comparing key lists. A value pulled out by key can be cast to its real type, which lets guest limits sort as numbers.

The listing editor logs each save as a JSON document in a VARCHAR column named payload. json_extract pulls a fragment out by path and returns it as JSON, so a string comes back wrapped in its quotes and an object comes back whole. That suits a fragment headed for more JSON processing, and it gets in the way when you want a plain value to filter, group or join on.

json_extract_scalar(payload, '$.address.city') returns the value as VARCHAR with the quotes removed. It handles strings, numbers and booleans. A path that lands on an object or an array, a path that does not exist, and a JSON null all give SQL NULL.

CAST stops the whole query when one value will not convert, and event 4's price is TBD. TRY_CAST returns NULL for that row and lets the others through. Wrapped around the extraction, it gives a column that aggregates cleanly, and COUNT over that column reports how many rows had a usable price.

json_extract_scalar returns NULL for $.amenities and $.address, which leaves the payload's arrays and objects out of reach. To search, sort or clean them with the functions from earlier sections, convert the JSON into native types: CAST(json_value AS ARRAY(VARCHAR)) or CAST(json_value AS MAP(VARCHAR, VARCHAR)).

The cast needs a JSON-typed input. json_extract(payload, '$.amenities') supplies one, since it returns JSON. When a whole VARCHAR column is one JSON document, json_parse(payload) turns the text into JSON. Casting the VARCHAR straight to ARRAY fails, because a string has no conversion to an array; the text has to be parsed first.

Each JSON array element becomes one SQL element, and a JSON null element becomes a SQL NULL. Event 3's amenities arrive as [kitchen, NULL, parking], the same blank slot the first 2 sections handled, and the same cleanup applies once the cast is done. The has_hot_tub column reads the uncleaned array on purpose: listing 103 gets NULL, and pointing contains at clean would give false.

An object casts to a map with its field names as keys. JSON object keys are always strings, so the key type is VARCHAR. The value type is yours to pick, and every value has to convert to it. All fields of address are strings, so MAP(VARCHAR, VARCHAR) fits, and element_at returns NULL for the zip that event 4 never sent.

The full payload mixes numbers, booleans, an object and an array, and no single SQL type holds them all. JSON does: casting to MAP(VARCHAR, JSON) keeps each value as a JSON fragment, so the cast succeeds on any object. map_keys of that map lists the fields each document carries. Event 4 has no superhost key, while event 3 carries a nightly_price key whose value is null.

CAST converts all or nothing. If one element in one row cannot become the target type, the query stops with an error. TRY_CAST returns NULL for that row's entire value, never a partial array. The table shows what each input becomes as ARRAY(INTEGER).

Take the whole payload. TRY_CAST(json_parse(payload) AS MAP(VARCHAR, INTEGER)) returns NULL on all 4 events: every event carries an address object and an amenities array, and neither converts to an integer, so each guest limit is lost with them. Cast to MAP(VARCHAR, JSON) instead and all 4 succeed, while TRY_CAST(json_extract_scalar(payload, '$.max_guests') AS INTEGER) still reads 6 for event 4.