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 address JSON object members, one can use one of the following:

• .keyName addresses the value of the object member with the name keyName.
• ."keyName" - the same as above but allows to address object member whose name is not a valid identifier (i.e. has spaces and other characters)
• .* - addresses the values of all members of the object.

Array elements

To address array elements, one can use 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, **, selects all elements. The wildcard step cannot be the last element in the path.

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.