Search code examples
sqljsonoracle-databasestored-proceduresjson-value

How to use Oracle JSON_VALUE


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?


Solution

  • 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