Some of JSON functions accept JSON Path expressions. MariaDB defines path as follows:
JSON Path Syntax
path : '$' [step]*
$ symbol represents the scope being searched. The scope is always the entire document, so all JSON Paths start with
The scope is followed by zero or more steps, which select specific element(s) in the JSON document.
Each step may be one of the following:
- Object member
- Array element
To select member(s) in a JSON object, one can use one of the following:
.memberNameselects the value of the member with name memberName.
."memberName"- the same as above but allows to select a member with a name that's not a valid identifier (that is, has space, dot, and/or other characters)
.*- selects the values of all members of the object.
if the current scope is an array, nothing will be selected.
To select elements of an array, one can use one of the following:
[N]selects element number N in the array. The elements are counted from zero.
[*]selects all elements in the array.
The wildcard step,
**, recursively selects all child array elements and object members of the context node.
The wildcard step cannot be the last step in the JSONPath expression. It must be followed by an array or object member selection step. For example:
select json_extract(@json_doc, '$**.price');
will select all object members in the document that are named
select json_extract(@json_doc, '$**');
will select the second element in each of the arrays present in the document.
Each node will be returned once, although it's possible that one of the returned nodes will contain another.
lax mode selector
A JSON path expression may be prefixed with
lax , so that expression looks like
MariaDB's JSONPath syntax is a proper subset of JSON Path's definition in the SQL Standard.
MySQL's JSONPath is close to MySQL's JSONPath. The exceptions are: MySQL supports
[M to N] as array element selectors, but doesn't allow one to specify the lax or strict mode selector.