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