JSONPath expressions

You are viewing an old version of this article. View the current version here.

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 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 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.

lax mode selector

A JSON path expression may be prefixed with lax , so that expression looks like lax $.memberName


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, but doesn't allow one to specify the lax or strict mode selector.


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.