maria-developers team mailing list archive

[Alexey Botchkov <holyfoot@xxxxxxxxxxx>]

>  What do you think?

Returning the JSON subdocument would violate the standard. I think it makes
sense as
the user normally expects the scalar value and if there is the JSON
subdocument in the
searched field, it's rather an error and must be detected.

I'd add the FORMAT JSON keyword to handle this explicitly.
The Oracle implementation of the FORMAT JSON seems weird to me as it
returns NULLs
on the scalar values instead. Didn't decide to myself though if it's the
standard way to handle it.
The scalar value is actually the valid json.

Regards.
HF




On Thu, Feb 4, 2021 at 2:44 PM Sergey Petrunia <sergey@xxxxxxxxxxx> wrote:

> Hi Alexey,
>
> Consider this:
>
> select * from
> json_table(
>   '{"a":[1,2]}',
>   '$' columns (
>      jsn_path json path '$.a' default '{}' on empty
>     )
> ) as tt;
>
> MySQL produces:
> +----------+
> | jsn_path |
> +----------+
> | [1, 2]   |
> +----------+
>
> MariaDB produces:
> +----------+
> | jsn_path |
> +----------+
> | NULL     |
> +----------+
>
> As far as I understand, MySQL is extending the standard here.
> The standard specifies that one can extract JSON subdocuments with "FORMAT
> JSON" syntax:
>
> <JSON table formatted column definition> ::=
>   <column name> <data type>
>     FORMAT <JSON representation>
>     [ PATH <JSON table column path specification> ]
>
> as opposed to regular:
>
> <JSON table regular column definition> ::=
>   <column name> <data type>
>   [ PATH <JSON table column path specification> ]
>
> OracleDB accepts FORMAT JSON clause:
> https://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973
>
> select * from
> json_table(
>   '{"a":[1,2]}',
>   '$' columns (
>      jsn_path varchar(100) format json path '$.a'
>     )
> ) as tt;
>
> produces the same output as MySQL does.
>
> I think, MariaDB's current behavior - produce NULL when path points to a
> JSON
> sub-document, produce invalid JSON when the path points to a constant - is
> not acceptable.
>
> I'm fine if we just disable this and return ER_NOT_IMPLEMENTED.
>
> Alternatively, we could return JSON subdocuments like MySQL does.
>
> What do you think?
>
> BR
>  Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
>
>
>