By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH t(arrayCars) AS
(
SELECT JSON_QUERY('{"Cars": [{"name":"Honda", "color":"red" },
{"name":"Toyota", "color":"green"}] }', '$.Cars')
FROM dual
)
SELECT name, color
FROM t
CROSS JOIN JSON_TABLE(arrayCars,
'$' COLUMNS(NESTED PATH '$[*]'
COLUMNS(
name VARCHAR2(100) PATH '$.name',
color VARCHAR2(100) PATH '$.color'
)
)
)
| NAME | COLOR |
|---|---|
| Honda | red |
| Toyota | green |