JSONPath expressions

Some of JSON functions accept JSON Path expressions. MariaDB defines path as follows:

JSON Path Syntax

path : '$' [step]*

The $ 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
• Wildcard

Object members

To select member(s) in a JSON object, one can use one of the following:

• .memberName selects 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.

Array elements

To select elements of an array, one can use one the following:

• [N] selects element number N in the array. The elements are counted from zero.
• [*] selects all elements in the array.

Wildcard

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 price, while

select json_extract(@json_doc, '$**[2]');

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.

Compatibility

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 [last] and [M to N] as array element selectors.