JSON_EXTRACT_SCALAR()

Concepts covered: JSON Extraction

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.

SELECT
  event_id,
  json_extract_scalar(payload, '$.listing_id') AS listing_id,
  json_extract_scalar(payload, '$.address.city') AS city,
  json_extract_scalar(payload, '$.address') AS address,
  json_extract_scalar(payload, '$.nightly_price') AS price,
  json_extract_scalar(payload, '$.superhost') AS superhost
FROM listing_events

Event 3's price is NULL because its JSON holds null, and event 4's superhost is NULL because the key is absent. The column cannot tell those 2 cases apart, and the next section shows how to list the keys each document carries. Event 4's price arrives as the text TBD, which matters as soon as the column is cast.

Paths into nested fields and arrays

Path syntax in this lesson

• $.max_guests: a top-level key.
• $.address.city: a key inside a nested object, one dot per level.
• $.amenities[0]: the first element of a JSON array. Path indexes start at 0, while SQL array positions start at 1.
• $.amenities: a whole array, so json_extract_scalar returns NULL for it.

Applied to the 4 save events, those paths reach into the address object and pick amenities by index. Event 4 returns NULL twice, for 2 different reasons: its address carries no zip key, and its third amenity is a JSON null. Index 2 is the third element, so event 1 shows parking in that column.

SELECT
  event_id,
  json_extract_scalar(payload, '$.address.zip') AS zip,
  json_extract_scalar(payload, '$.amenities[0]') AS first_amenity,
  json_extract_scalar(payload, '$.amenities[2]') AS third_amenity
FROM listing_events

Every result is VARCHAR

Numbers come out as text, and text compares character by character. Sorting guest limits of 4, 10, 2 and 6 in descending order as text gives 6, 4, 2, 10, because the character 6 outranks the character 1. The first tab sorts the raw strings. The second casts to INTEGER inside the SELECT, so the sort sees numbers.

Text sort

Sorts VARCHAR values: listing 102 with 10 guests lands last.

SELECT
  json_extract_scalar(payload, '$.listing_id') AS listing_id,
  json_extract_scalar(payload, '$.max_guests') AS max_guests
FROM listing_events
ORDER BY max_guests DESC

Cast first

Sorts INTEGER values: listing 102 comes first.

SELECT
  json_extract_scalar(payload, '$.listing_id') AS listing_id,
  CAST(json_extract_scalar(payload, '$.max_guests') AS INTEGER) AS max_guests
FROM listing_events
ORDER BY max_guests DESC

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.

SELECT
  json_extract_scalar(payload, '$.address.city') AS city,
  COUNT(*) AS listings,
  COUNT(TRY_CAST(json_extract_scalar(payload, '$.nightly_price') AS INTEGER)) AS priced,
  AVG(TRY_CAST(json_extract_scalar(payload, '$.nightly_price') AS INTEGER)) AS avg_price
FROM listing_events
GROUP BY json_extract_scalar(payload, '$.address.city')
ORDER BY city

Event 1's payload holds "amenities": ["wifi", "hot_tub", "parking"]. What does this query return for it?

SELECT
  json_extract_scalar(payload, '$.amenities[1]') AS amenity
FROM listing_events
WHERE event_id = 1

• hot_tub, because path indexes start at 0 and the result is unquoted VARCHAR
• wifi, because indexes start at 1 here, as they do for element_at
• "hot_tub" in quotes, because the value comes out of a JSON array
• NULL, because json_extract_scalar returns NULL for any path into an 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.