Oracle 12c: Remove JSON object from JSON array

Question

Need to create a function which takes input of CLOB and I need to remove array matching the condition.

create or replace FUNCTION remove_config_node_by_key (
    p_in_json   IN   CLOB,
    p_in_key    IN   VARCHAR2
) RETURN CLOB IS
    l_ja   json_array_t;
    l_po   json_object_t;
    l_key  VARCHAR2(500);
BEGIN
    l_ja := json_array_t.parse(p_in_json);
    FOR idx IN 0.. l_ja.get_size - 1 LOOP
        l_po := json_object_t(l_ja.get(idx));
        l_key := l_po.get_string('key');
        -- check if the key matches with input and then delete that node.
        dbms_output.put('Key to remove in the JSON: ' || l_key);
        IF l_key = p_in_key THEN
            dbms_output.put('Key to remove in the JSON: ' || l_key);
           l_ja.remove (idx);
         --   dbms_output.new_line;
           dbms_output.put('Key is removed in the JSON: ' || l_key);
        END IF;

    END LOOP;

    RETURN l_ja.to_clob;
END;

When called with:

update COLD_DRINKS cd set cd.configuration = remove_config_node_by_key(cd.configuration, 'b') 
where country='INDIA';

I get error:

Error report -
ORA-30625: method dispatch on NULL SELF argument is disallowed
ORA-06512: at "SYS.JSON_OBJECT_T", line 72
ORA-06512: at "PLATFORM_ADMIN_DATA.REMOVE_CONFIG_NODE_BY_KEY", line 11

Sample JSON:

[
    {
        "key": "a",
        "value": "lemon soda"
    },
    {
        "key": "b",
        "value": "Coke"
    },
    {
        "key": "c",
        "value": "Pepsi"
    }
    
]

I think something is wrong about this l_ja.remove (idx); as this one causes the exception. Not able to remove the object at index.

Answer 1

The issue was resolved when I added REVERSE in for loop

before [ERROR]

FOR idx IN 0.. l_ja.get_size - 1 

after [PASS]

FOR idx IN REVERSE 0.. l_ja.get_size - 1 

Complete working function

CREATE OR REPLACE FUNCTION remove_config_node_by_key (
    p_in_json   IN   CLOB,
    p_in_key    IN   VARCHAR2
) RETURN CLOB IS
    l_ja   json_array_t := json_array_t ();
    l_po   json_object_t;
    l_key  VARCHAR2(500);
BEGIN
    l_ja := json_array_t.parse(p_in_json);
    FOR idx IN REVERSE 0.. l_ja.get_size - 1 
    LOOP
        l_po := json_object_t(l_ja.get(idx));
        l_key := l_po.get_string('key');
        -- check if the key matches with input and then delete that node.
        IF l_key = p_in_key THEN
            dbms_output.put_line('Key to remove in the JSON: ' || l_key || ' at index : ' || idx);
            l_ja.remove (idx);
           dbms_output.put_line('Key is removed in the JSON: ' || l_key);
        END IF;
    END LOOP;

    RETURN l_ja.to_clob;
END;
/