JSON Extraction Patterns

Concepts covered: JSON Extraction

Objects inside objects

Production payloads nest. The order_events table carries the complete order: a customer object with its own keys, an items list where every element is an object, a tags list of strings, and the tip.

A path can step through several keys. '$.customer.plan' reads the top-level customer object, then the plan key inside it. Each dot is one step down, and the result is whatever sits at the end of the path: a string here, but a number, a list or another object are all possible.

Lists and positions

Square brackets step into a list by position, and JSON paths count positions from 0. '$.items[0]' is the first element of the list and '$.items[1]' the second. Positions and keys chain: '$.items[0].name' reads the first item, then its name. To count the elements rather than read one, wrap the extracted list in JSON_ARRAY_LENGTH.

SELECT
  order_id,
  JSON_EXTRACT(payload, '$.customer.plan') AS plan,
  JSON_EXTRACT(payload, '$.items[0].name') AS first_item,
  JSON_ARRAY_LENGTH(JSON_EXTRACT(payload, '$.items')) AS item_count
FROM order_events

Order 5003 has 3 items and its first is Margherita. Order 5004 has an empty items list: the length is 0, and $.items[0].name returns NULL because there is no first item to step into. JSON_ARRAY_LENGTH returns a BIGINT rather than a JSON value, so item_count is ready for SUM, AVG or a HAVING filter with no conversion.

Pulling out a whole branch

A path does not have to end on a scalar. Stop it on an object or a list and JSON_EXTRACT hands back that entire branch as JSON text. The second item of each order, '$.items[1]', comes back as a complete object with its braces and quotes, or as NULL where the order has fewer than 2 items.

SELECT
  order_id,
  JSON_EXTRACT(payload, '$.items[1]') AS second_item,
  JSON_EXTRACT(payload, '$.tags') AS tags
FROM order_events

Branch extraction is how you hand a sub-document to a later step. The tags value here is still JSON, a list drawn with its brackets and quoted strings; the CAST for Complex Types section turns it into a SQL array you can search with CONTAINS.

Paths that find nothing

Three different mistakes produce the same quiet NULL: a key that does not exist, a position past the end of a list, and a typo in the path. '$.customer.email' returns NULL on every row because no order stores an email. '$.items[5]' returns NULL because no order has 6 items. '$.custmer.plan' returns NULL on every row as well, and nothing warns you. When a new extraction returns NULL everywhere, read the path letter by letter against one real payload before you blame the data.

For order 5001, whose items are [{"name":"Pad Thai","qty":1},{"name":"Thai Iced Tea","qty":2}], what does JSON_EXTRACT(payload, '$.items[2].qty') return?

• 2, the quantity of the Thai Iced Tea
• 1, the quantity of the Pad Thai
• An error, because the list has 2 items
• NULL, because position 2 is past the end

Tracing a path step by step

Reading a path left to right, one step at a time, is the surest way to predict what it returns. Here is '$.items[1].name' traced through order 5003, whose list holds Margherita, Garlic Knots and Lemonade.

Each step needs something to land on. Run the same path on order 5002, whose list holds only the Burrito, and step 3 finds no second object, so the whole path returns NULL. Swap .name for .qty at step 4 and order 5003 returns the bare number 1.

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.