I'm working on a trigger.
declare
v_time number(11, 0);
begin
for i in 0..1
loop
select JSON_VALUE(body, '$.sections[0].capsules[0].timer.seconds') into v_time from bodycontent where contentid=1081438;
dbms_output.put_line(v_time);
end loop;
end;
However, index references do not become dynamic.
like JSON_VALUE(body, '$.sections[i].capsules[i].timer.seconds')
Is there any way I can do this?
You can use JSON_TABLE
:
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;
/
Which, for the test data:
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
);
Outputs:
0 4
Or, you can just use a query:
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;
Which outputs:
SECTION_INDEX | CAPSULE_INDEX | TIME ------------: | ------------: | ---: 1 | 1 | 0 2 | 2 | 4
(Note: the indexes from FOR ORDINALITY
are 1 higher than the array indexes in the JSON path.)
db<>fiddle here