maria-developers team mailing list archive

[Alexey Botchkov <holyfoot@xxxxxxxxxxx>]

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 <sergey@xxxxxxxxxxx> wrote:

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