Hi Alexey,

I've found this discrepancy in JSONPath evaluation:


set @json_doc3 =
'
{
  "root": {
     "child1" : {
       "child2" : {
         "child1" : {
           "x":124
          }
        }
      }
   }
}
';

select json_extract(@json_doc3, '$**.child1**.x');

MariaDB [test]> select json_extract(@json_doc3, '$**.child1**.x');
+--------------------------------------------+
| json_extract(@json_doc3, '$**.child1**.x') |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+
1 row in set (0.001 sec)

MySQL-8> select json_extract(@json_doc3, '$**.child1**.x');
+--------------------------------------------+
| json_extract(@json_doc3, '$**.child1**.x') |
+--------------------------------------------+
| [124]                                      |
+--------------------------------------------+
1 row in set (0.00 sec)

Which one is right?

My opinion is that MariaDB's answer is definitely incorrect.

As for MySQL's answer, it depends on how the result of JSONPath expression is
defined.

If it is "a set of nodes in the JSON document which match the pattern", MySQL's
result is correct.

Coming from XPath world, I was expecting a semantics in form

find the set of nodes matching the search step#1 ( $**,
  then apply step #2 ( .child1)
    then apply step #3 ( any children of those)
      ...

in which case "124" would be in the result twice as it is reachable via two
possible paths.

Any thoughts about this?

BR
 Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog