|
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 |
… |
|