SELECT VERSION();
| VERSION() |
|---|
| 8.0.13 |
| … |
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 '999' ON ERROR DEFAULT '111' ON EMPTY,
aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY,
bx INT EXISTS PATH "$.b"
)
) AS tt;
| rowid | ac | aj | bx |
|---|---|---|---|
| 1 | 3 | "3" | 0 |
| 2 | 2 | 2 | 0 |
| 3 | 111 | {"x": 333} | 1 |
| 4 | 0 | 0 | 0 |
| 5 | 999 | [1, 2] | 0 |
| … | |||