Looping through whole json_object_t in oracle to change values of specific keys

charly99
May 9, 2024
224 views
0 votes
2 Answers

I’m writing a function in oracle 19c, which should, on given json_object_t that can have any structure, loop through all the keys, and if key is one of pre-specified, mask the value with asterisk. Those values would be card numbers. The function should then return changed json_object_t.

My problem is that the returned json is unchanged.

Here is my code. The input json is just an example; it should work with any json. Code for now is written as pl/sql block:

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
          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());
            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());
                    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;

the return v_output is the same as input:

  {
        "success": true,
        "payload": {
            "authSumCnt": "1",
            "CardNum": "7712343649057813",
            "authSum": [
                {
                    "CardNumber": "9512343649057813",
                    "otherKey": "otherValue"
                },
                {
                    "acctNum": "1234567890123456",
                    "anotherKey": "anotherValue",
                    "nestedArray": [
                        {
                            "nestedKey": "nestedValue",
                            "acctNumber": "1234567890123456"
                        }
                    ]
                }
            ]
        }
    }

Answers

The problem is that you are parsing the string values to new JSON objects, and those are then independent of the original input parameter.

So rather than:

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

just get the actual object:

            v_json_object := p_json.get_object(v_key);

and instead of:

            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());

get the original array and it’s element, as an object:

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

So in full:

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

Which produces (formatted):

{
  "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

For the fun and ≥23c only, take advantage of the JS regexp supporting back reference:

create or replace mle module regexp_mod language javascript as

export function regexp_replace(str, regexp, param, replacestr) {
    var re = new RegExp(regexp, param);
    return str == null ? '' : str.replace(re, replacestr == null? '':replacestr);
}

-- add other JS regexp functions you may need...

end;
/

create or replace function regexp_replace_fnc (
  str string,
  re string, param string, replacestr string)
  return string
as mle module regexp_mod
signature 'regexp_replace(string, string,string, string)';
/

select 
    regexp_replace_fnc(js, '"(w+)Num([^"]*)"s*:s*(?<quote>[''"])(d{4})(.*?)(d{4})k<quote>', 'g', '"$1Num$2": "$4******$6"')
    as newjs
from data;
/


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

Please signup or login to give your own answer.

Click here to cancel reply.