在Oracle中循环整个json_opp_t以更改特定键的值

问题在于，您正在将字符串值解析为新的SON对象，而这些对象则独立于原始的输入参数.

所以，而不是:

            v_json_object := JSON_OBJECT_T.parse(v_value.to_string());

只需获取实际对象:

            v_json_object := p_json.get_object(v_key);

而不是:

            v_json_array := JSON_ARRAY_T.parse(v_value.to_string());
...
                v_json_object := JSON_OBJECT_T.parse(v_json_array.get(j).to_string());

获取原始数组及其元素，作为对象:

            v_json_array := p_json.get_array(v_key);
...
                v_json_object := new JSON_OBJECT_T(v_json_array.get(j));

所以全文:

DECLARE
  v_output CLOB;
  v_input_json JSON_OBJECT_T;
  v_json_object JSON_OBJECT_T;

  PROCEDURE mask_keys(p_json IN OUT NOCOPY JSON_OBJECT_T)
  IS
    v_keys JSON_KEY_LIST;
    v_key VARCHAR2(100);
    v_value JSON_ELEMENT_T;
    v_json_array JSON_ARRAY_T := JSON_ARRAY_T();
    v_index NUMBER;
    TYPE param_values_t IS TABLE OF VARCHAR2(100); 
    v_param_values param_values_t := param_values_t('CardNum', 'CardNumber', 'acctNum', 'acctNumber'); --keys to mask

    FUNCTION mask_numeric_value(p_value VARCHAR2) RETURN VARCHAR2 IS --function to mask the value
        v_masked_value VARCHAR2(100);
        v_value varchar2(100) := p_value;
    BEGIN
    dbms_output.put_line('function mask_numeric_value - start'); 
    v_value := trim(BOTH '"' from v_value);
        IF REGEXP_LIKE(v_value, '^\d+$') THEN
            v_masked_value := SUBSTR(v_value, 1, 6) || '******' || SUBSTR(v_value, -4);
        ELSE
            v_masked_value := v_value;
        END IF;
        RETURN v_masked_value;
    END mask_numeric_value;
  BEGIN
    DBMS_OUTPUT.PUT_LINE('function mask_keys - start');
      v_keys := p_json.get_keys;
        FOR i IN 1..v_keys.count LOOP
          v_key := v_keys(i);
          v_value := p_json.get(v_key);
          DBMS_OUTPUT.PUT_LINE('Key: ' || v_key || ', Value: ' || v_value.to_string);
          IF v_key MEMBER OF v_param_values THEN
            dbms_output.put_line('Found'); 
            IF v_value.is_string THEN
              dbms_output.put_line('Masking'); 
              p_json.put(v_key, mask_numeric_value(v_value.to_string()));
              DBMS_OUTPUT.PUT_LINE('New value: '||p_json.get(v_key).to_string);
            END IF;
          ELSIF v_value.is_object THEN
            DBMS_OUTPUT.PUT_LINE('Value is an object');
            DBMS_OUTPUT.PUT_LINE(v_value.to_string);
/*
            v_json_object := JSON_OBJECT_T.parse(v_value.to_string());
*/
            -- If the element is an object, recursively call mask_keys
            v_json_object := p_json.get_object(v_key);
            mask_keys(v_json_object);
          ELSIF v_value.is_array THEN
            DBMS_OUTPUT.PUT_LINE('Value is an array');
/*
            v_json_array := JSON_ARRAY_T.parse(v_value.to_string());
*/
            v_json_array := p_json.get_array(v_key);
            FOR j IN 0..v_json_array.get_size() - 1 LOOP
              -- If the array element is an object, recursively call mask_keys
              IF v_json_array.get(j).is_object THEN
                DBMS_OUTPUT.PUT_LINE('Element is an object');
/*
                v_json_object := JSON_OBJECT_T.parse(v_json_array.get(j).to_string());
*/
                v_json_object := new JSON_OBJECT_T(v_json_array.get(j));
                mask_keys(v_json_object);
              END IF;
            END LOOP;
          END IF;
        END LOOP;
  END mask_keys;

BEGIN
    v_input_json := JSON_OBJECT_T.parse('{
        "success": true,
        "payload": {
            "authSumCnt": "1",
            "CardNum": "7712343649057813",
            "authSum": [
                {
                    "CardNumber": "9512343649057813",
                    "otherKey": "otherValue"
                },
                {
                    "acctNum": "1234567890123456",
                    "anotherKey": "anotherValue",
                    "nestedArray": [
                        {
                            "nestedKey": "nestedValue",
                            "acctNumber": "1234567890123456"
                        }
                    ]
                }
            ]
        }
    }');

    mask_keys(v_input_json);
    v_output := v_input_json.to_clob;
    DBMS_OUTPUT.PUT_LINE(v_output);
END;
/

它产生(格式化):

{
  "success": true,
  "payload": {
    "authSumCnt": "1",
    "authSum": [
      {
        "otherKey": "otherValue",
        "CardNumber": "951234******7813"
      },
      {
        "anotherKey": "anotherValue",
        "nestedArray": [
          {
            "nestedKey": "nestedValue",
            "acctNumber": "123456******3456"
          }
        ],
        "acctNum": "123456******3456"
      }
    ],
    "CardNum": "771234******7813"
  }
}

fiddle