CAST for Complex Types

Concepts covered: Type Casting

Why a JSON value needs converting

Every value JSON_EXTRACT returns is of type JSON, including the ones that look like text or numbers. The engine will not compare a JSON value with = to a VARCHAR literal, and will not add JSON numbers with SUM or average them with AVG. Grouping by an uncast JSON value does run, but the group labels keep their quotes, "dashpass" rather than dashpass, and stay JSON rather than text you can filter or join on. CAST to VARCHAR or INTEGER fixes all 3. The query below is the first thing most people try, and it fails.

SELECT
  order_id
FROM order_meta
WHERE JSON_EXTRACT(payload, '$.plan') = 'dashpass'

Scalar casts

CAST to VARCHAR turns a JSON string into plain text and drops the quotes. CAST to INTEGER turns a JSON number into a whole number, CAST to BOOLEAN turns true or false into a SQL boolean, and both a missing value and a JSON null become SQL NULL through any cast.

With the plan cast to VARCHAR the engine can group by it, and with the tip cast to INTEGER, SUM can add it. The query below totals tips per plan over order_meta.

SELECT
  CAST(JSON_EXTRACT(payload, '$.plan') AS VARCHAR) AS plan,
  SUM(CAST(JSON_EXTRACT(payload, '$.tip') AS INTEGER)) AS total_tips
FROM order_meta
GROUP BY CAST(JSON_EXTRACT(payload, '$.plan') AS VARCHAR)

The 2 dashpass orders tipped 3 and 5 for a total of 8; the 2 standard orders tipped 0 and 2 for a total of 2. Write the CAST expression in GROUP BY exactly as it appears in SELECT, because GROUP BY does not see the alias. The same cast unlocks WHERE: the next query keeps the contactless orders.

SELECT
  order_id,
  CAST(JSON_EXTRACT(payload, '$.plan') AS VARCHAR) AS plan,
  CAST(JSON_EXTRACT(payload, '$.contactless') AS BOOLEAN) AS contactless
FROM order_meta
WHERE CAST(JSON_EXTRACT(payload, '$.contactless') AS BOOLEAN) = true

Order 5002 drops out because its value is false. Order 5004 drops out because its cast returns NULL, and NULL = true is neither true nor false under three-valued logic, so WHERE discards the row. Two rows survive, and the plan column now reads dashpass with no quotes.

JSON lists become arrays

CAST to ARRAY(VARCHAR) turns a JSON list of strings into a SQL array, and from there everything in the arrays section applies. The question from the intro, how many orders included a drink, is a CONTAINS on the cast tags list.

SELECT
  order_id,
  CAST(JSON_EXTRACT(payload, '$.tags') AS ARRAY(VARCHAR)) AS tags,
  CONTAINS(CAST(JSON_EXTRACT(payload, '$.tags') AS ARRAY(VARCHAR)), 'drink') AS has_drink
FROM order_events

The empty JSON list on order 5004 casts to an empty array, and CONTAINS on an empty array is false. Move the CONTAINS into WHERE and count the rows that pass to get the growth team's number.

SELECT
  COUNT(*) AS drink_orders
FROM order_events
WHERE CONTAINS(CAST(JSON_EXTRACT(payload, '$.tags') AS ARRAY(VARCHAR)), 'drink')

Two of the 4 orders carried a drink, and nobody added a column to find out.

JSON objects become maps

CAST to MAP(VARCHAR, VARCHAR) turns a JSON object into a map keyed by its field names. Every value is converted to the map's value type, so the customer id 88, a JSON number, becomes the text 88 here. Lookups then use the map bracket from the maps section, customer['plan'].

SELECT
  order_id,
  CAST(JSON_EXTRACT(payload, '$.customer') AS MAP(VARCHAR, VARCHAR)) AS customer
FROM order_events

Text to JSON, and back

Casting text to JSON does something most people do not expect: CAST(payload AS JSON) wraps the entire string as one JSON string value, braces and all, rather than reading its structure. JSON_PARSE(payload) reads the structure and returns the document as a JSON value you can extract from. Going the other way, JSON_FORMAT(json_value) returns the JSON text as a VARCHAR, which is how you store an extracted branch back into a text column.

CAST(text AS JSON)

• Treats the text as one string value
• '{"a":1}' becomes the JSON string "{\"a\":1}"
• A path such as $.a then finds nothing

JSON_PARSE(text)

• Reads the text as a JSON document
• '{"a":1}' becomes the JSON object {"a":1}
• A path such as $.a then finds 1

Do

• CAST a JSON value to VARCHAR before comparing it in WHERE or grouping by it
• CAST a JSON number to INTEGER before SUM or AVG
• CAST a JSON list to ARRAY(VARCHAR) and search it with CONTAINS
• Use JSON_PARSE to turn stored text into a JSON document

Avoid

• Compare a bare JSON_EXTRACT result to a string literal
• Expect CAST(text AS JSON) to read the structure inside the text
• Repeat a path in GROUP BY with a different spelling than SELECT

Order 5001's payload stores "plan":"dashpass". Which expression returns the VARCHAR value dashpass with no quotes around it?

• JSON_EXTRACT(payload, '$.plan') on its own
• CAST(JSON_EXTRACT(payload, '$.plan') AS VARCHAR)
• CAST(payload AS VARCHAR) on the whole column
• CAST(JSON_EXTRACT(payload, '$.plan') AS JSON)

What GROUP BY can read

Counting orders per plan puts the cast to work in GROUP BY, where 2 mistakes stop the query. GROUP BY reads the columns of the FROM table, so GROUP BY plan fails: order_meta has order_id and payload, and the alias plan exists only in the SELECT list. A GROUP BY expression that differs from the selected one fails too, because the selected plan is then neither grouped nor aggregated.

The growth team wants the number of orders on each plan in order_meta, with the plan as plain text. Arrange the tiles into a query the engine runs; 2 tiles hold a GROUP BY it rejects.

About this lesson

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