- 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.
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:
and you can find
bar in the grand-children of
but there’s no way to search for
bar everywhere in the tree. MariaDB, MySQL and PostgreSQL have extensions for recursive searches:
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:
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.