support MySQL's JSON_TABLE

[lovasoa]

MySQL has a JSON_TABLE table-valued function with the following syntax:

JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string path [on_empty] [on_error]
    |  name type EXISTS PATH string path
    |  NESTED [PATH] path COLUMNS (column_list)

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

Example

SELECT * FROM
 JSON_TABLE(
         '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
         "$[*]"
         COLUMNS(
           rowid FOR ORDINALITY,
           ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR,
           aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
           bx INT EXISTS PATH "$.b"
         )
) AS tt;