JSON_EXTRACT and the Arrow Operators in MySQL Explained
How MySQL JSON paths work, what JSON_EXTRACT returns for each value type, why -> and ->> are not the same, and the path rules that cause errors.
Every read from a MySQL JSON column goes through a path. You write $.specs.dims.w and get a number back, or $.tags[0] and get a string with quotes on it, and somewhere between the two a lot of confusion begins. This article takes the path language and the three extraction forms apart: what the syntax allows, what comes back for each kind of value, how the arrow operators relate to the functions, and the errors that appear when you write a path that looks reasonable.
The three forms
json_extract(doc, '$.brand') -- returns JSON
doc -> '$.brand' -- same as json_extract
doc ->> '$.brand' -- json_unquote(json_extract(doc, '$.brand'))-> and ->> are only syntactic sugar, and the sugar has one rule: the left side must be a column. cast('{"a":1}' as json) -> '$.a' is a syntax error near ->, and so is json_extract(@d, '$.a.b') -> '$[0]'. In those places use json_extract and json_unquote. In a query on a table, the operators read better, and the optimizer treats them identically to the functions.
The difference between -> and ->> is the JSON string quote. On row 1 of the product table, attrs -> '$.brand' returned "Globex" and attrs ->> '$.brand' returned Globex. Concatenation shows the effect plainly: concat(json_extract(attrs, '$.brand'), '!') is "Globex"!, and concat(attrs ->> '$.brand', '!') is Globex!.
The path language
A path starts with $, which is the document, and then applies steps. All of these ran on the same test document:
| Path | Meaning | Result |
|---|---|---|
$.a.b[0] | key, key, element 0 | 10 |
$.a.b[2].c | into the object at index 2 | "deep" |
$.a.b[last] | last element | {"c": "deep"} |
$.a.b[last-1] | the one before the last | 20 |
$.a.b[0 to 1] | a range | [10, 20] |
$.a.b[*] | every element | [10, 20, {"c": "deep"}] |
$.a.b[*].c | c of every element that has one | ["deep"] |
$.arr[1][0] | nested arrays | 3 |
$.arr[*][1] | second item of each inner array | [2, 4] |
$**.c | key c at any depth | ["deep"] |
$."a b" | key with a space (quoted) | 3 |
$."x.y" | key with a dot (quoted) | 4 |
Two details that cost people time. First, keys are case sensitive and quoting is part of the path: the test document had both Key and key, and $.Key returned 1 while $.key returned 2. A key with a space, a dot or other punctuation needs double quotes inside the path string, as in '$."a b"'; without them the path is an error or reads as something else, because $.x.y means key x and then key y, not the key x.y. Second, wildcards return arrays: $.a.b[*].c returned ["deep"] even though one value matched. A path that can match more than one place returns an array, and that array is what your code receives, so unwrap it deliberately. $**.c finds all c keys at any depth and also returns an array.
When you give json_extract several paths, it returns the matches in an array in the order of the paths: json_extract(@d, '$.t', '$.num') gave ["text", 7].
What is not allowed
A negative index.
$.a.b[-1]raisedERROR 3143: Invalid JSON path expression. The error is around character position 6. Use[last]and[last-1].**A path without the leading
$.**json_extract('{"a": 1}', 'a')fails with the same error. Always begin with$.Expressions inside the brackets beyond
last,last-N, a number,*anda to b. There is no filter syntax here; for conditions on elements, useJSON_TABLE(JSON_TABLE in MySQL) orJSON_SEARCH.
An index past the end, $.a.b[10], is not an error. It returns SQL NULL, the same as a missing key.
What each type looks like on the way out
json_extract returns a JSON value, and the display of that value is what trips people. Using one value of each type:
path json_extract json_type json_unquote(json_extract(...))
$.t "text" STRING text
$.num 7 INTEGER 7
$.f 1.5 DOUBLE 1.5
$.bool false BOOLEAN false
$.n null NULL null
$.zzz NULL (SQL) NULL NULL
$.a.b [10, 20, ...] ARRAY [10, 20, {"c": "deep"}]Strings come back with quotes. Numbers, booleans and null do not need any. json_unquote removes the quotes and un-escapes a JSON string (json_unquote('"a\\"b"') returned a"b, and é became é), while on a non-string value it returns the JSON text unchanged. In particular json_unquote of a JSON null returned the four-character string null, not SQL NULL, which is why where doc ->> '$.x' is null does not find keys set to null. json_type prints NULL for both a JSON null and a missing path; check json_extract(...) is null for the SQL NULL.
Comparing the results
Because the value is still JSON, MySQL compares it with JSON rules when the other side is a JSON value, and converts when it is a string or number literal:
json_extract(@d, '$.num') = 7 1
json_extract(@d, '$.num') = '7' 0
json_extract(@d, '$.num') + 1 8
json_extract(@d, '$.t') = '"t"' 0 (the value is "text", so 't' and '"t"' never match)A JSON number equals a numeric literal, but not the string '7'. A JSON string "text" is not equal to the SQL string text until you unquote. That is the main reason to prefer ->> when comparing text: attrs ->> '$.brand' = 'Acme' works the way it reads. For numbers, either leave the value as JSON and compare it with a number (attrs -> '$.price' > 450 counted 20,061 rows), or extract text and cast (cast(attrs ->> '$.price' as decimal(8,2)) > 450, also 20,061). Comparing the text with a string (attrs ->> '$.price' > '450') counted 43,661 and is wrong. The hub guide, using the JSON column type in MySQL, has the whole set.
Using a path from a variable or parameter
The path is just a string, so it can come from a user variable, a concat or a prepared-statement parameter:
set @p = '$.a.b[2].c';
select json_extract(@d, @p); -- "deep"
prepare st from 'select json_extract(?, ?)';
set @doc = '{"k": 5}', @path = '$.k';
execute st using @doc, @path; -- 5Both worked. That is useful for generic code, and it carries the usual risk: a path built from user input is a path chosen by the user, and a bad one raises error 3143. Whitelist the paths, or validate them against a pattern before use. The operators -> and ->> need a literal path string (they do not accept a variable or expression on the right), so a dynamic path means json_extract.
Extraction inside generated columns and indexes
The same expressions are what you put in a generated column. attrs ->> '$.sku' in a generated always as (...) clause is stored or computed per row, and an index on that column makes the lookup fast. For types, json_value(attrs, '$.price' returning decimal(8,2)) (8.0.21 and later) extracts and casts in one step, with null on error or error on error. See indexing a MySQL JSON field with a generated column for the details.
Reading many paths at once
Calling json_extract ten times on the same column is ten decodes of the document. MySQL's binary format makes each lookup cheap, but if you need ten fields, JSON_TABLE reads them in one pass and gives typed columns:
select jt.* from products p,
json_table(p.attrs, '$' columns (
sku varchar(20) path '$.sku',
brand varchar(20) path '$.brand',
price decimal(8,2) path '$.price')) jt
where p.id = 1;For one or two fields the arrows are fine. In the guide's benchmark MySQL's penalty over typed columns was a factor of 1.5 for a group-by on two extracted fields, so extraction is not where you lose.
When a path expression is not enough
A path addresses a place; it does not search. These are cases where json_extract is the wrong tool.
You need to filter elements of an array by a property. Paths cannot say "the item whose sku is B2". JSON_TABLE with a where, or JSON_SEARCH for string values, can (searching values inside a MySQL JSON array).
The key is not known in advance. json_keys(doc) lists the keys, and JSON_TABLE with a path wildcard iterates values, but turning key names into rows needs JSON_TABLE over json_keys and a second lookup.
You need the whole structure transformed. JSON_MERGE_PATCH, JSON_OBJECT and JSON_ARRAYAGG build new documents, covered in building JSON output with JSON_OBJECT and JSON_ARRAYAGG.
The path must be unit tested. A typo silently returns SQL NULL. Check with json_contains_path(doc, 'one', '$.path') in a test, or assert row counts.
The syntax is defined in the MySQL manual, Searching and modifying JSON values, and the path grammar is shown in the JSON path syntax section. A browser tool for trying a path against a document is the JSONPath tester on this site; MySQL's path syntax is a subset with its own last and to forms, so confirm the final expression in the mysql client.
