Question 110224 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Oracle 12c: Remove JSON object from JSON array

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.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

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

  2. 0

    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

    Login or Signup to reply.
  3. 0

    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…)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search