JSON_EXTRACT()

Concepts covered: JSON Extraction

One column, many fields

A JSON column is a text column with structure inside it. The text follows the JSON format: an object is a set of key-value pairs inside curly braces, a list is a sequence of values inside square brackets, and each value is a string in double quotes, a number, true, false, or null. The order_meta table below holds a single order per row, and everything the app recorded about that order sits in the payload column.

Teams store events this way because the shape keeps changing. When the app adds a scheduled_for field next month, nobody alters the table; the new key appears inside payload on new rows and old rows carry on without it. Order 5004 already shows the pattern: its payload has no contactless key, because that order was placed before the option existed.

Reaching in with a path

JSON_EXTRACT(column, path) reads a single value out of the JSON text. The path is a string that starts with $, which stands for the whole document, and .key steps into one key. So '$.plan' means the value stored under plan at the top level, and '$.tip' means the value under tip. Play the query below and watch each path land on its value in every row.

SELECT
  order_id,
  JSON_EXTRACT(payload, '$.plan') AS plan,
  JSON_EXTRACT(payload, '$.tip') AS tip
FROM order_meta

Row by row, the path $.plan finds dashpass, standard, dashpass, standard, and $.tip finds 3, 0, 5, 2. The plan values come out wrapped in double quotes while the tips do not. That difference is the central fact about this function: it returns a value of type JSON, a fragment of JSON text, so a JSON string keeps the quotes it had in the document and a JSON number stays bare.

What comes back

Add a third extraction for the contactless key and run the query. Order 5004 has no such key, and the result for that row shows how the function handles absence.

SELECT
  order_id,
  JSON_EXTRACT(payload, '$.plan') AS plan,
  JSON_EXTRACT(payload, '$.tip') AS tip,
  JSON_EXTRACT(payload, '$.contactless') AS contactless
FROM order_meta

Order 5004 returns NULL for contactless because its payload lacks the key. The function raises no error for a missing key; it reports NULL and moves on, which is what you want when older events lack newer fields, and what you must remember when a typo in the path quietly returns NULL for every row.

Order 5002's payload is {"plan":"standard","tip":0,"contactless":false}. What does JSON_EXTRACT(payload, '$.tip') return for that row?

• "0", a JSON string holding the digit
• 0, a JSON number with no quotes
• NULL, because a value of 0 counts as empty
• false, the value that follows tip in the text

Spelling a path

The engine checks the form of every path when the query runs. A path opens with $, puts a dot before each key, and ends on a key. 'plan' has no $, '$plan' has no dot, and '$.plan.' ends on a dot with nothing after it, so each one stops the query with an invalid-path error. Form is all it checks. Keys match letter for letter, including case, so '$.Plan' is well formed, runs, and comes back NULL for all 4 orders, because every document spells the key plan.

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.