json_extract & the ->> operator

Query JSON inside a column

Lesson 15 of 20 · 10 min · Module 4: Hierarchies, series and JSON

Modern databases often store flexible details as JSON: a mug has a capacity, tea has an origin. SQLite can read inside that JSON, so you can filter and sort by fields that are not real columns.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Read fields from JSON text
Filter and sort by JSON values
Decide when JSON is the right choice

The idea

json_extract(attributes, '$.color') returns the color from the JSON text, or NULL when it is missing. The shorter attributes ->> '$.color' does the same. Values keep their JSON type, so numbers compare as numbers.

SELECT name, attributes ->> '$.origin' AS origin
FROM products
WHERE category = 'Pantry';

01Paths start with $

$ is the whole document, $.color a field, and $.sizes[0] the first item of an array. A missing path returns NULL instead of an error.

02Types survive

capacity_ml is stored as a JSON number, so ->> returns an integer and sorting is numeric. A value stored as "900" in quotes would sort as text.

SELECT '{"n": 900}' ->> '$.n' + 1;

Output

03Columns for what you query often

JSON is ideal for optional details that differ between products. Fields used in many filters, joins, or constraints are better as real columns, which can be indexed and validated easily.

Your turn

Return name, color and capacity_ml (both read from attributes) for products whose material is ceramic or steel. Sort by capacity, largest first.

What the checks look for

Finds the ceramic and steel products with their color and capacity

Try it in the editor →

Take this with you

JSON columns give flexibility; json_extract and ->> give access.

Go a little further

Use json_each(attributes) to list every attribute name used across all products and how often it appears.