On JSON Path in MariaDB

Short version:

• JSON Path in MariaDB is now documented: https://mariadb.com/kb/en/jsonpath-expressions/
• It supports a subset of JSON Path from SQL:2016. The subset is close to what MySQL supports.
• It does have an extension to do recursive searches.
• The search for matches is done “on the fly”, without constructing the document tree.

Longer version:

I got interested in JSON Path for two reasons: first, I was using it to extract portions of Optimizer Trace. Second, I am reviewing the patch for JSON_TABLE.

Unfortunately there wasn’t a description of MariaDB’s JSON Path dialect anywhere. Did everybody think paths are too obvious and something like foo.bar.baz is too trivial to document?

Well, it’s is not that simple. The definition of JSON Path in SQL:2016 standard takes 23 pages. Path expression can include filters, functions and arithmetic expressions (MariaDB doesn’t support any of those). One thing that the standard does NOT include is a capability to do recursive searches. You can ask for all members of an object or an array:

$.foo.*

and you can find bar in the grand-children of foo:

$.foo.*.bar

but there’s no way to search for bar everywhere in the tree. MariaDB, MySQL and PostgreSQL have extensions for recursive searches:

$**.bar

will search for bar everywhere in the document. (In PostgreSQL, the syntax is slightly different: $.**.bar). This is very useful for querying the Optimizer Trace:

select
json_extract(trace, '$**.analyzing_range_alternatives'))
from
information_schema.optimizer_trace

Some notes about the implementation

MariaDB doesn’t support filter conditions or [last] as array index. Which means we can walk through JSON document, and as soon as we see a node, we can tell whether it is a match or not. There is no need to build the parse tree or perform some kind of look-ahead.

As far as I understand, MySQL may or may not build the parse (DOM) tree, depending on the properties of the search path.