SQL & PL/SQL

Hi,

I'm replacing some older 3rd party json code with apex_json which is going well. However, there is one feature that seems to be missing or I'm missing the method of doing it. I'd like to parse and loop through a bunch of nodes in a clob (formed of JSON) and when a certain condition is met, remove the element I am processing if it meets a certain criteria from a copy of the clob that is passed out of the procedure.

I've got an silly example that follows where the aim is to remove the banana element!

Is this possible please? Note that I am on 11g and so don't have the luxury of the 12c features yet.

Thank you

Phil

declare
  j          apex_json.t_values;
  l_members  WWV_FLOW_T_VARCHAR2;
  l_count    number;
  
  in_clob          clob; 
  out_clob clob;
BEGIN
  
  in_clob:= '{
  "nodes":{ 
           "node_name": "TestNode",
           "node_number": 123,
           "records":
           [
            {"RecordId":1,"fruit":"Apple","Quantity":"12"},
            {"RecordId":3,"fruit":"Orange","Quantity":"13"},
            {"RecordId":7,"fruit":"Banana","Quantity":"17"}
           ]
          }      
       }';
  out_clob:=in_clob;
  
  apex_json.initialize_clob_output;
  apex_json.parse(j, in_clob);
  dbms_output.put_line('Nodes member count: ' || apex_json.get_count(p_path => '.', p_values => j)); --< just 1 "nodes"
  
  dbms_output.put_line('-- -- -- -- -- -- -- -- -- --');
  
  l_members := apex_json.get_members(p_path => 'nodes', p_values => j); --< gets everything below nodes
  dbms_output.put_line('Nodes Child Members Count : ' || l_members.COUNT);
  
  FOR i IN 1 .. l_members.COUNT LOOP
    dbms_output.put_line('Member Item Idx   : ' || i);
    dbms_output.put_line('Member Name       : ' || l_members(i));
  END LOOP; ---for x in (select * from apex_json.get_members(p_path=>'.',p_values=>j)) loop
  dbms_output.put_line('-- -- -- -- -- -- -- -- -- --');
  
  -- Attemt to process nodes/records array
  l_count := apex_json.get_count(p_path => 'nodes.records', p_values => j); 
  dbms_output.put_line('Record Count   : ' || l_count);
  
  FOR i IN 1 .. l_count LOOP
    dbms_output.put_line('RecordId - ' ||
                         apex_json.get_varchar2(p_values => j,
                                                p_path   => 'nodes.records[%d].RecordId',
                                                p0       => i));
    IF apex_json.get_varchar2(p_values => j,
                              p_path   => 'nodes.records[%d].fruit',
                              p0       => i) = 'Banana' THEN
      -- Found the offending element, remove from out_clob????
      dbms_output.put_line('found banana');
    END IF;                                             
  END LOOP;
END;