maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #12521
MDEV-17399: Columns of JSON datatype?
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
Follow ups
References