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