PLSQL looping through JSON object

Question

Basically I have a json that looks like this [{"group":"groupa","status":"active"},{"group":"groupb","status":"inactive"}] and I want to loop through and extract the group only and save them in a variable in order to loop and compare the groups to a certain variable.

for example

group := 'groupc'
while counter < jsonGroup.count
loop
if jsonGroup(counter) := group then ....

is there any way to save the group into jsonGroup as an array or table?

thank you

Answer 1

From Oracle 12, you can use JSON PL/SQL object types to iterate over the JSON array and to extract the value of the group attribute of the objects:

DECLARE
  value VARCHAR2(4000) := '[{"group":"groupa","status":"active"},{"group":"groupb","status":"inactive"}]';
  ja JSON_ARRAY_T := JSON_ARRAY_T.PARSE(value);
  je JSON_ELEMENT_T;
  grp VARCHAR2(20);
  i  PLS_INTEGER := 0;
BEGIN
  LOOP
    je := ja.GET(i);
    EXIT WHEN je IS NULL;
    grp := TREAT(je AS JSON_OBJECT_T).get_string('group');
    DBMS_OUTPUT.PUT_LINE(grp);
    i := i + 1;
  END LOOP;
END;
/

Which outputs:

groupa
groupb