主页 / dba / 问题 / 322174
Accepted
Deepak Negi
Asked: 2023-01-17 16:32:20 +0800 CST

Oracle 12c：从 JSON 数组中删除 JSON 对象

  • 772

需要创建一个接受 CLOB 输入的函数，我需要删除符合条件的数组。

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;

调用时：

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"
    }
    
]

我认为这个 l_ja.remove (idx); 有问题。因为这个会导致异常。无法删除索引处的对象。

oracle-12c

1 个回答

  1. Best Answer

    当我在 for 循环中添加 REVERSE 时问题得到解决

    [错误]之前

    FOR idx IN 0.. l_ja.get_size - 1

    [通过]后

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

    完备的工作功能

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

相关问题