Oracle 12c中从JSON数组删除指定JSON对象的函数报错问题

问题场景 #

编写了remove_config_node_by_key函数，用于接收CLOB类型的JSON数组，删除其中key匹配指定值的对象。执行以下UPDATE语句时触发ORA-30625错误：

UPDATE cold_drinks cd 
   SET cd.configuration = remove_config_node_by_key(cd.configuration, 'b') 
 WHERE country='INDIA';

报错信息：

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

输入JSON示例：

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

期望执行后的JSON：

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

原函数代码：

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;

错误原因 #

1. 正向遍历删除导致索引错位：当你在正向循环中删除数组元素时，数组长度会立即缩短。比如删除索引1的元素后，原来索引2的元素会移动到索引1的位置，但循环会继续执行下一个索引（2），此时访问的是已经不存在的位置，返回NULL。将NULL传入json_object_t构造器时，就会触发ORA-30625错误。
2. 未处理NULL输入：如果数据库中configuration字段存在NULL值，json_array_t.parse(p_in_json)会直接报错，不过当前的错误主要由第一个原因导致。

修正后的函数 #

采用倒序遍历的方式，从数组最后一个元素往前遍历，这样删除元素不会影响未遍历的元素索引：

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
    -- 处理NULL输入，直接返回NULL
    IF p_in_json IS NULL OR p_in_key IS NULL THEN
        RETURN p_in_json;
    END IF;

    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');
        
        IF l_key = p_in_key THEN
            l_ja.remove(idx);
            dbms_output.put_line('已删除key为' || l_key || '的元素');
        END IF;
    END LOOP;

    RETURN l_ja.to_clob;
END;
/

修改说明 #

• 增加了NULL输入判断，避免传入NULL时触发解析错误。
• 将循环改为倒序遍历，删除元素后不会影响前面未遍历的元素索引，彻底解决了访问NULL的问题。
• 调整了日志输出，让信息更清晰。

内容的提问来源于stack exchange，提问作者Deepak Negi