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

input JSON:

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

Expected JSON after execution:

[
    {
        "key": "a",
        "value": "lemon soda"
    },
    {
        "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.

CodePudding user response：

In 18c at least it works with your sample data (with the trailing comma removed from the array), but it gets that error with a null configuration.

So you can either test for null in your function, or exclude nulls from your update, or fix your data so it doesn't have nulls.

The simplest thing to do is probably add a null check:

...
BEGIN
    IF p_in_json IS NULL THEN
        RETURN NULL;
    END IF;
    l_ja := json_array_t.parse(p_in_json);
...

fiddle

CodePudding user response：

You can also remove it using json_transform:

create or replace function remove_config_node_by_key (
    p_in_json   IN   CLOB,
    p_in_key    IN   VARCHAR2
) RETURN CLOB IS
    l_result CLOB ;
begin
    execute immediate q'~select json_transform(
        :p_in_json,
        REMOVE '$[*]?(@.key == "~' || p_in_key || q'~")'
     )
     from dual~' into l_result using p_in_json  
     ;
     return l_result ;
end ;
/

(with all usual comments regarding possible SQL injection...)

CodePudding user response：

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;
/