Remove Json Node Oracle

Question

i plan to remove a number of nodes from oracle json. Looking thru different resources I have come up with these steps which work fine. The issue I have is that my initial select statement is based on 2-3 millions rows so the DBMS_OUTPUT.put_line is not useful. How can I remove the nodes and have the result returned similar with the result of a Select statement?

CREATE table t1 (
  id           NUMBER,
  description  VARCHAR2(30),
  js clob
);

INSERT INTO t1 VALUES (1, 'The value 1', '{
  "id": "13",
  "plan": "200",
  "age": "14"
}');

COMMIT;


SET SERVEROUTPUT ON
DECLARE
  l_obj JSON_OBJECT_T   ;
  l VARCHAR2(4000 BYTE);
CURSOR c1 IS
    SELECT 'plan' as name FROM dual union select 'age' as name FROM dual;
    
CURSOR c2 IS
    SELECT js as test FROM t1;
     
BEGIN
  l_obj := JSON_OBJECT_T;
  l_obj := NEW JSON_OBJECT_T();
for l in c2 LOOP
  l_obj := JSON_OBJECT_T.parse(l.test);
  DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);
FOR item IN c1
  LOOP
  l_obj.remove(item.name);
  DBMS_OUTPUT.put_line('l_obj.stringify = ' || l_obj.stringify);
END LOOP;
END LOOP;
END;
/

Thanks!

Answer 1

As you're on 12c you could put your PL/SQL code into a WITH clause function, and simplify it a bit:

WITH
  FUNCTION prune (p_json CLOB) RETURN CLOB IS
    l_obj JSON_OBJECT_T;
  BEGIN
    l_obj := JSON_OBJECT_T.parse(p_json);
    FOR item IN (
      SELECT 'plan' AS name FROM dual
      UNION ALL
      SELECT 'age' AS name FROM dual
    )
    LOOP
      l_obj.REMOVE(item.name);
    END LOOP;
    RETURN l_obj.TO_CLOB();
  END;
SELECT prune(t1.js) AS result
FROM t1;

db<>fiddle - 18c but should work in 12c too.

That defines a 'local' function in the WITH clause, which I've called prune; that takes in your initial CLOB value, converts to a JSON object, and loops to remove the nodes are you were doing before; and then returns the result as a CLOB to match the input, rather than using stringify which returns a more length-constrained VARCHAR2 value. Then the final select just calls that local prune function for every value in the table.