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@mariadb.com> 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