CAST JSON to Array/Map

Concepts covered: Type Casting

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.

JSON arrays into SQL arrays

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.

WITH parsed AS (
  SELECT
    json_extract_scalar(payload, '$.listing_id') AS listing_id,
    CAST(json_extract(payload, '$.amenities') AS ARRAY(VARCHAR)) AS amenities
  FROM listing_events
)
SELECT
  listing_id,
  amenities,
  array_except(amenities, ARRAY[CAST(NULL AS VARCHAR)]) AS clean,
  contains(amenities, 'hot_tub') AS has_hot_tub
FROM parsed

Event 7's payload holds "amenities": ["wifi", null, "wifi"]. What does this query return?

SELECT
  cardinality(array_distinct(CAST(json_extract(payload, '$.amenities') AS ARRAY(VARCHAR)))) AS n
FROM listing_events
WHERE event_id = 7

• 1, because array_distinct drops the NULL along with the second wifi
• 3, because the cast keeps all 3 elements and cardinality counts them
• 2, because array_distinct keeps one wifi and one NULL, and both count
• NULL, because a JSON null element makes the whole cast fail

JSON objects into maps

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.

SELECT
  json_extract_scalar(payload, '$.listing_id') AS listing_id,
  element_at(CAST(json_extract(payload, '$.address') AS MAP(VARCHAR, VARCHAR)), 'zip') AS zip,
  cardinality(CAST(json_extract(payload, '$.address') AS MAP(VARCHAR, VARCHAR))) AS address_fields
FROM listing_events

Mixed values: MAP(VARCHAR, JSON)

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.

SELECT
  event_id,
  array_sort(map_keys(CAST(json_parse(payload) AS MAP(VARCHAR, JSON)))) AS fields,
  contains(map_keys(CAST(json_parse(payload) AS MAP(VARCHAR, JSON))), 'superhost') AS has_superhost
FROM listing_events

When a cast meets the wrong data

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.

Return the listing_id of every event in listing_events whose amenities array contains 'hot_tub'. The amenities live inside the VARCHAR column payload. Two tiles are distractors.

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.