Hi, Sergey.
To me the XPath version seems correct here. So both we and MySQL are wrong.
Though the JSON_Extract function is the MySQL-s invention so it's tempting
to
make it working like they do.
So i'm in doubt how to fix this.
A.
On Fri, Nov 20, 2020 at 12:18 AM Sergey Petrunia
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