![](https://secure.gravatar.com/avatar/c7422c379fec4aa4d99a8a137bf10594.jpg?s=120&d=mm&r=g)
== Short == I've discovered another mismatch between MariaDB implementation and MySQL. I don't think we need to fix it. == Long == select * from json_table( '[{"a":"aa"}, {"b":"bb"}]', '$[*]' columns ( col1 varchar(100) path '$.b' default 'bbb' on empty ) ) as tt; In MySQL, this gives an error: ERROR 3141 (22032): Invalid JSON text in argument 1 to function JSON_TABLE: "Invalid value." at position 0. and the cause is that MySQL expects the default value to be JSON. Their documentation says: DEFAULT json_string ON EMPTY I wasn't able to find the reasoning behind this. MariaDB's behavior makes more sense to me: MariaDB: +------+ | col1 | +------+ | bbb | | bb | +------+ Oracle DB produces the same. In order to get "bbb" as the default for MySQL, one needs to quote it: select * from json_table( '[{"a":"aa"}, {"b":"bb"}]', '$[*]' columns ( col1 varchar(100) path '$.b' default '"bbb"' on empty ) ) as tt; MySQL: +------+ | col1 | +------+ | bbb | | bb | +------+ MariaDB and OracleDB: +-------+ | col1 | +-------+ | "bbb" | | bb | +-------+ == Attempt to locate where this is described in the SQL Standard == Section 7.11, <JSON table>, General Rules: e, ii, 2: 2) If JTCDi is a <JSON table regular column definition>, then: A) ... let ZBi be the <JSON table column empty behavior> ... ... C) The General Rules of Subclause 9.40, “Casting an SQL/JSON sequence to an SQL type”, are applied with ST1 as STATUS IN, SEQ as SQL/JSON SEQUENCE, ZBi as EMPTY BEHAVIOR ... Looking at that section: 9.40 Casting an SQL/JSON sequence to an SQL type 4) If TEMPST is successful completion, then: ... b) If the length of SEQ is 1 (one), then let I be the SQL/JSON item in SEQ. ... iii) Otherwise, let IDT be the data type of I. ... 2) Otherwise, let X be an SQL variable whose value is I. Let V be the value of CAST (X AS DT) BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net