By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE bodycontent ( body CLOB CHECK ( body IS JSON ), contentid NUMBER );
INSERT INTO bodycontent ( body, contentid ) VALUES (
'{"sections":[
{"capsules":[{"timer":{"seconds":0}},{"timer":{"seconds":1}},{"timer":{"seconds":2}}]},
{"capsules":[{"timer":{"seconds":3}},{"timer":{"seconds":4}},{"timer":{"seconds":5}}]},
{"capsules":[{"timer":{"seconds":6}},{"timer":{"seconds":7}},{"timer":{"seconds":8}}]}]}',
1081438
)
1 rows affected
declare
v_time number(11, 0);
begin
for i in 0..1 loop
SELECT time
INTO v_time
FROM bodycontent b
CROSS APPLY
JSON_TABLE(
b.body,
'$.sections[*]'
COLUMNS (
section_index FOR ORDINALITY,
NESTED PATH '$.capsules[*]'
COLUMNS (
capsule_index FOR ORDINALITY,
time NUMBER(11,0) PATH '$.timer.seconds'
)
)
) j
WHERE j.section_index = i + 1
AND j.capsule_index = i + 1
AND b.contentid=1081438;
dbms_output.put_line(v_time);
end loop;
end;
/
1 rows affected
dbms_output:
0
4
SELECT section_index, capsule_index, time
FROM bodycontent b
CROSS APPLY
JSON_TABLE(
b.body,
'$.sections[*]'
COLUMNS (
section_index FOR ORDINALITY,
NESTED PATH '$.capsules[*]'
COLUMNS (
capsule_index FOR ORDINALITY,
time NUMBER(11,0) PATH '$.timer.seconds'
)
)
) j
WHERE ( j.section_index, j.capsule_index) IN ( (1,1), (2,2) )
AND b.contentid=1081438;
| SECTION_INDEX | CAPSULE_INDEX | TIME |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 2 | 4 |