JSON_EXTRACT Path Navigation

Concepts covered: JSON Extraction

Every engine reads a value out of JSON by following a path: a route from the top of the document to one value. Most engines write the path as a JSONPath string. $ is the root, .key steps into a key of an object, and [n] steps into a position of an array, counted from 0. Postgres spells each step as an operator and Snowflake has its own colon syntax, but all of them walk the route one step at a time, and each step only works if the value it lands on has the shape the next step expects.

Here is event 2 in full. The animation in the previous section showed only its first 2 keys; real purchase payloads also carry an amount alongside 2 nested structures, a device object and an items array.

{
  "user_id": "u_101",
  "event_type": "purchase",
  "amount": 49.90,
  "device": {"platform": "ios", "app_version": "7.2.0"},
  "items": [
    {"product_id": "p_88", "qty": 2},
    {"product_id": "p_12", "qty": 1}
  ]
}

Each step of $.items[0].product_id narrows the value: the object, then its array, then the first element, then one string.

$ (source, the whole payload object)
.items (transform, an array of 2 objects)
[0] (transform, {product_id: p_88, qty: 2})
.product_id (consumer, p_88)

$ -> .items
.items -> [0]
[0] -> .product_id

Read the path $.items[0].product_id against the diagram. The root is an object, so .items is legal and lands on an array. An array has positions, so [0] is legal and lands on the first object in the cart. That object has keys, so .product_id lands on the string p_88. Each step is a question asked of whatever value the previous step returned.

Path	What the steps do	Value on event 2
`$.user_id`	key at the root	u_101
`$.device.platform`	into device, then its platform key	ios
`$.device.app_version`	into device, then app_version	7.2.0
`$.items[0].product_id`	into items, first element, its product_id	p_88
`$.items[1].qty`	into items, second element, its qty	1
`$.items[2].product_id`	items holds only positions 0 and 1	NULL

The same path in 5 engines

-- First product in the cart: one path, 5 engines

-- MySQL and Trino: JSON_EXTRACT with a JSONPath string
-- returns "p_88" with its JSON quotes; the next section explains why
SELECT JSON_EXTRACT(payload, '$.items[0].product_id') AS first_product FROM events;
-- bare p_88: payload->>'$.items[0].product_id' in MySQL, json_extract_scalar in Trino

-- BigQuery: JSON_VALUE for a scalar at the end of the path
SELECT JSON_VALUE(payload, '$.items[0].product_id') AS first_product FROM events;

-- Postgres: one operator per step, ->> on the last
SELECT payload->'items'->0->>'product_id' AS first_product FROM events;

-- Snowflake: colon into the VARIANT, then dots and brackets
SELECT payload:items[0].product_id::STRING AS first_product FROM events;

-- Spark SQL
SELECT get_json_object(payload, '$.items[0].product_id') AS first_product FROM events;

Postgres is the one that looks different. Its operators take one step each: -> with a key name steps into an object, -> with an integer steps into an array, and the chain reads left to right in the same order as the path. When the path is long, #>> takes all the steps at once as a text array: payload #>> '{items,0,product_id}' returns the same p_88. Snowflake starts with a colon after the column and continues with dots and brackets; ::STRING at the end casts the result.

Keys with a dash or a dot in the name need quoting inside a path string, because the dot would otherwise read as a step. MySQL takes $."user-agent" and Snowflake takes payload:"user-agent". Postgres needs nothing extra: payload->'user-agent' already passes the key name as a string, so every character in it is part of the name.

Path mistakes that cost time

The follow-up: the last item

A common push once the first item works: 'carts have different lengths, so how do you get the last one?' A fixed index stops working, and the answer depends on the engine. Postgres jsonb accepts a negative index, payload->'items'->-1. MySQL paths accept $.items[last]. Snowflake takes a computed position through GET: GET(payload:items, ARRAY_SIZE(payload:items) - 1). Naming the engine feature in one sentence scores the point; the array length behind the Snowflake form is the subject of the last section.

About this lesson

This section is part of the Semi-Structured 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.