JSON path syntax, casting from variant, and the NULL-vs-missing-key distinction all bite when extracting semi-structured data.

JSON's numbers survive the wire as characters, and the extraction function's return type — not the value's looks — decides comparison semantics: text ordering, MAX='9', silent coercions replayed one layer up, where it's easier to miss because the source was 'a number'. The casting order of operations: extract raw → branch on null-vs-missing if it matters → cast with the failure mode chosen (::int errors; TRY_CAST returns NULL). Payload drift is the recurring producer.

See it break

prizes

payload
{"score":85}
{"score":9}

The trap

SELECT
  json_extract_string(payload, '$.score') AS score
FROM
  prizes
ORDER BY
  json_extract_string(payload, '$.score') ;

The fix

SELECT
  CAST(json_extract_string(payload, '$.score') AS INT) AS score
FROM
  prizes
ORDER BY
  CAST(json_extract_string(payload, '$.score') AS INT);

The trap

The fix

The leaderboard put '85' before '9' — values extracted from JSON are text until you type them, so they compare alphabetically; casting to INT after extraction orders them 9 then 85.

The rule

Extract → distinguish → cast, with TRY/strict chosen; typed accessor functions where the engine offers them.

payload ->> 'user_id'

Shows up elsewhere

Implicit casts in comparisons JSON null vs missing key

Try one →Prove it in practice →