By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE response (
payload CLOB
CHECK( payload IS JSON )
);
INSERT INTO response ( payload ) VALUES (
'{
"type": "com.xpto.abc.impl.JobResponse",
"data": {"responses": {"666a3c3f-666-45cb-aa50-666930b42fd7": ["XML1", "XML2"]}}
}'
)
1 rows affected
select tipo,
seq,
response
from response r
CROSS APPLY
json_table(
r.payload,
'$'
columns
tipo VARCHAR2(64) PATH '$.type',
NESTED PATH '$.data.responses.*[*]'
COLUMNS (
seq FOR ORDINALITY,
response VARCHAR2 PATH '$'
)
);
| TIPO | SEQ | RESPONSE |
|---|---|---|
| com.xpto.abc.impl.JobResponse | 1 | XML1 |
| com.xpto.abc.impl.JobResponse | 2 | XML2 |