P18 · SQL & Querying
SQL JSON — extract, cast, and the sort trap
How SQL extracts fields from JSON — and the trap where extracted values are text, so a numeric leaderboard sorts 85 above 9 with no error.
Start with the basics
Modern warehouses store more than flat columns — JSON blobs, arrays, nested structures — and SQL has grown functions to reach inside them.
You extract a field from JSON by a path, and you explode an array into rows to work with its elements. Both are routine, both have a catch about types and blanks.
The one to internalize first: a value pulled out of JSON is text until you convert it. That single fact explains most of the sorting and comparison surprises.
One that works
| payload |
|---|
| {"score":85} |
SELECT CAST(
json_extract_string(payload, '$.score') AS INT
) AS score
FROM prizes;| score |
|---|
| 85 |
Extract gives text; casting to a number makes it sort and compare numerically.
Values pulled out of JSON are text until you cast them — so numbers sort and compare as strings until you convert them.
When to use it
- Reach for JSON functions when a column holds a blob you need a field from.
- Cast extracted values to their real type before you sort, compare, or do math on them.
- Explode an array into rows when you need to work element by element, then re-aggregate.
That one fact — values come out of JSON as text — is where a sort quietly lies. Numbers pulled from JSON compare as strings until you convert them, so 85 sorts before 9. Here is the day that bites.
See it happen
The leaderboard that ranks 85 above 9 — text vs number
Scores live in JSON payloads. You extract and sort them. Watch the leaderboard rank 85 above 9, because the extracted values are still text.
| payload |
|---|
| {"score":85} |
| {"score":9} |
extract only — sorts as text
SELECT json_extract_string(payload, '$.score') AS score
FROM prizes
ORDER BY score;| score |
|---|
| 85 |
| 9 |
cast to INT — sorts as number
SELECT CAST(json_extract_string(payload, '$.score') AS INT) AS score
FROM prizes
ORDER BY CAST(json_extract_string(payload, '$.score') AS INT);| score |
|---|
| 9 |
| 85 |
The leaderboard put '85' before '9' — values extracted from JSON are text until you type them, so they compare alphabetically; casting to a number orders them 9 then 85.
Walk the full example in the unit →The rest of the family
More ways JSON and arrays bite
A key present-but-null and a key that is absent are different, and your extraction function decides whether you can still tell them apart.
Exploding an array into rows deliberately re-introduces fan-out, which a later re-aggregation closes back up.
Where supported, QUALIFY lets you filter on a window function's result without a wrapping subquery.
A contains-or-overlaps test on an array differs in cost and missing-value behavior from joining through an explode.
Exploding an array multiplies the parent row by the list length, so parent-level totals computed afterward are inflated.
A membership test answers 'is it in the list' without exploding, where an explode-join answers it at a different grain and must be re-deduped.
In typed nested data a missing field is a compile error; in schemaless JSON a missing key is just a runtime blank.
Self-check
How to spot it in your own SQL
- Numbers extracted from JSON sort or compare in the wrong order.
- You can't tell a missing key from a present-but-blank one.
- A total inflated after you exploded an array into rows.
- A JSON path returns blank and you expected a value.
What people miss
Semi-structured data looks like normal columns until you sort it. Values come out of JSON as text, so numbers compare alphabetically and a leaderboard quietly ranks 85 above 9 — no error, just wrong.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why do my JSON numbers sort in the wrong order?
Values extracted from JSON come out as text, and text sorts alphabetically — so '85' lands before '9'.
Cast the extracted value to a number before you sort or compare it.
from the unit →How do I tell a missing JSON key from a null value?
They are different facts — present-but-null versus absent — but some extraction functions flatten both to the same blank.
Use a function that distinguishes them, or check for the key's existence separately.
from the unit →Why did my total blow up after flattening an array?
Exploding an array multiplies the parent row once per element, so any parent-level total computed afterward is inflated by the list length.
Aggregate the exploded rows back to the parent grain before summing parent values, or test membership without exploding.
from the unit →Should I test array membership or unnest and join?
A membership test answers 'is it in the list' without exploding the row; an explode-and-join answers it at a finer grain and then needs re-deduping.
Prefer the membership test when you only need a yes/no; it is cheaper and keeps the grain.
from the unit →That’s the free taste