How to mask selected values in a json field - Postgresql 9.3 and PDI

Question

I have input field change_event (json datatype) which looks something like

[
{
"fieldName":"address",
"oldValue":{
"addressLine1":"36 ABC St",
"addressLine2":"Suite 1200",
"city":"Toronto",
"stateProvince":"ON",
"zipPostalCode":"M5C111",
"country":null
},
"newValue":{
"addressLine1":"36 ABC St",
"addressLine2":"Suite 1200",
"city":"Toronto",
"stateProvince":"ON",
"zipPostalCode":"M5C111",
"country":""
},
"reason":null
},
{
"fieldName":"email",
"oldValue":"test@gmail.com",
"newValue":"testemail@gmail.com",
"reason":null
},
{
"fieldName":"DATE_OF_BIRTH",
"oldValue":"1901-01-01",
"newValue":"1901-1-1",
"reason":null
},
{
"fieldName":"IS_VALIDATED",
"oldValue":"true",
"newValue":"true",
"reason":null
},
{
"fieldName":"MOBILE_NUMBER",
"oldValue":null,
"newValue":"",
"reason":null
},
{
"fieldName":"SALUTATION",
"oldValue":"",
"newValue":"Dr.",
"reason":null
},
{
"fieldName":"LANGUAGE",
"oldValue":"en",
"newValue":"en",
"reason":null
},
{
"fieldName":"firstName",
"oldValue":"JOHN",
"newValue":"JOHN",
"reason":null
},
{
"fieldName":"lastName",
"oldValue":"DOE",
"newValue":"DOE",
"reason":null
}
]

My requirement is to mask oldValue and newValue where fieldName in address,firstName, lastName, phoneNumber, and email before I load change_event field into the data warehouse.

I tried to do this in Pentaho by using Json Inputstep

After which I filtered the rows where fieldName in address,firstName, lastName, phoneNumber, and email. Masked (Postgresql MD5) oldValue and newValue values. Then tried to bring back all the fieldName's together using Json Output step.

But the end result had individual json's for each fieldName, oldValue, and newValue.

OUTPUT:

[{"fieldName":"DATE_OF_BIRTH","oldValue":"1901-01-01", "newValue":"1901-1-1"},{"fieldName":"IS_VALIDATED","oldValue":"true","newValue":"true"}
,{"fieldName":"LANGUAGE","oldValue":"en","newValue":"en"},
{"fieldName":"email","oldValue":"c2f72a3799a2bdb1c852c93466a356c5","newValue":"9cb14b499be76bb04fdbef92f29c744b"},{"fieldName":"MOBILE_NUMBER","oldValue":null,"newValue":null}
,{"fieldName":"SALUTATION","oldValue":null,"newValue":"2aaa2bf3b9a34ee7a4467803f7578f46"},{"fieldName":"firstName","oldValue":"e2577c04131c5b0c7e7580f978322b31","newValue":"e2577c04131c5b0c7e7580f978322b31"},{"fieldName":"lastName","oldValue":"85d05fd9229df84c06f2cbc6267e4fd7","newValue":"85d05fd9229df84c06f2cbc6267e4fd7"},{"fieldName":"address","oldValue":"a9bbba5830f6f33b979d03a9e832723a","newValue":"15e1212eeae2af35bc3deb10a2815e8f"}
]
    {"":[{"fieldName":"DATE_OF_BIRTH"},{"oldValue":"1901-01-01"},{"newValue":"1901-1-1"},{"fieldName":"IS_VALIDATED"},{"oldValue":"true"},{"newValue":"true"},{"fieldName":"LANGUAGE"},{"oldValue":"en"},{"newValue":"en"},{"fieldName":"address"},{"oldValue":"a9bbba5830f6f33b979d03a9e832723a"},{"newValue":"15e1212eeae2af35bc3deb10a2815e8f"},{"fieldName":"email"},{"oldValue":"c2f72a3799a2bdb1c852c93466a356c5"},{"newValue":"9cb14b499be76bb04fdbef92f29c744b"},{"fieldName":"MOBILE_NUMBER"},{"oldValue":null},{"newValue":null},{"fieldName":"SALUTATION"},{"oldValue":null},{"newValue":"2aaa2bf3b9a34ee7a4467803f7578f46"},{"fieldName":"firstName"},{"oldValue":"e2577c04131c5b0c7e7580f978322b31"},{"newValue":"e2577c04131c5b0c7e7580f978322b31"},{"fieldName":"lastName"},{"oldValue":"85d05fd9229df84c06f2cbc6267e4fd7"},{"newValue":"85d05fd9229df84c06f2cbc6267e4fd7"}]}

DESIRED OUTPUT:

[{"fieldName":"DATE_OF_BIRTH","oldValue":"1901-01-01", "newValue":"1901-1-1"},{"fieldName":"IS_VALIDATED","oldValue":"true","newValue":"true"}
,{"fieldName":"LANGUAGE","oldValue":"en","newValue":"en"},
{"fieldName":"email","oldValue":"c2f72a3799a2bdb1c852c93466a356c5","newValue":"9cb14b499be76bb04fdbef92f29c744b"},{"fieldName":"MOBILE_NUMBER","oldValue":null,"newValue":null}
,{"fieldName":"SALUTATION","oldValue":null,"newValue":"2aaa2bf3b9a34ee7a4467803f7578f46"},{"fieldName":"firstName","oldValue":"e2577c04131c5b0c7e7580f978322b31","newValue":"e2577c04131c5b0c7e7580f978322b31"},{"fieldName":"lastName","oldValue":"85d05fd9229df84c06f2cbc6267e4fd7","newValue":"85d05fd9229df84c06f2cbc6267e4fd7"},{"fieldName":"address","oldValue":"a9bbba5830f6f33b979d03a9e832723a","newValue":"15e1212eeae2af35bc3deb10a2815e8f"}
]

How to get the desired result. Thanks in advance.

Anyone. Who can help me out?? – DUnkn0wn1 Sep 08 '16 at 16:49 — DUnkn0wn1, Sep 08 '16 at 16:49

score 0 · Answer 1 · answered Sep 09 '16 at 14:33

Use a Modified Java Script Value step. Given an input field named json containing the json string: use the code below, and output json (Type:String, Replace:Y).

var old_obj = JSON.parse(json);
var new_obj = [];

for (var i = 0; i < old_obj.length; i++) {
  if (['address','firstName', 'lastName', 'phoneNumber', 'email'].indexOf(old_obj[i]["fieldName"]) < 0) {
    new_obj.push(old_obj[i]);
  }
}
json = JSON.stringify(new_obj);

score 0 · Answer 2 · answered Sep 12 '16 at 16:38

Thanks @matthiash. I ended up using similar logic to your's but using conditional masking (MD5) for each fieldName.

var MD5=function(s){function L(k,d){return(k<<d)|(k>>>(32-d))}function K(G,k){var I,d,F,H,x;F=(G&2147483648);H=(k&2147483648);I=(G&1073741824);d=(k&1073741824);x=(G&1073741823)+(k&1073741823);if(I&d){return(x^2147483648^F^H)}if(I|d){if(x&1073741824){return(x^3221225472^F^H)}else{return(x^1073741824^F^H)}}else{return(x^F^H)}}function r(d,F,k){return(d&F)|((~d)&k)}function q(d,F,k){return(d&k)|(F&(~k))}function p(d,F,k){return(d^F^k)}function n(d,F,k){return(F^(d|(~k)))}function u(G,F,aa,Z,k,H,I){G=K(G,K(K(r(F,aa,Z),k),I));return K(L(G,H),F)}function f(G,F,aa,Z,k,H,I){G=K(G,K(K(q(F,aa,Z),k),I));return K(L(G,H),F)}function D(G,F,aa,Z,k,H,I){G=K(G,K(K(p(F,aa,Z),k),I));return K(L(G,H),F)}function t(G,F,aa,Z,k,H,I){G=K(G,K(K(n(F,aa,Z),k),I));return K(L(G,H),F)}function e(G){var Z;var F=G.length;var x=F+8;var k=(x-(x%64))/64;var I=(k+1)*16;var aa=Array(I-1);var d=0;var H=0;while(H<F){Z=(H-(H%4))/4;d=(H%4)*8;aa[Z]=(aa[Z]| (G.charCodeAt(H)<<d));H++}Z=(H-(H%4))/4;d=(H%4)*8;aa[Z]=aa[Z]|(128<<d);aa[I-2]=F<<3;aa[I-1]=F>>>29;return aa}function B(x){var k="",F="",G,d;for(d=0;d<=3;d++){G=(x>>>(d*8))&255;F="0"+G.toString(16);k=k+F.substr(F.length-2,2)}return k}function J(k){k=k.replace(/rn/g,"n");var d="";for(var F=0;F<k.length;F++){var x=k.charCodeAt(F);if(x<128){d+=String.fromCharCode(x)}else{if((x>127)&&(x<2048)){d+=String.fromCharCode((x>>6)|192);d+=String.fromCharCode((x&63)|128)}else{d+=String.fromCharCode((x>>12)|224);d+=String.fromCharCode(((x>>6)&63)|128);d+=String.fromCharCode((x&63)|128)}}}return d}var C=Array();var P,h,E,v,g,Y,X,W,V;var S=7,Q=12,N=17,M=22;var A=5,z=9,y=14,w=20;var o=4,m=11,l=16,j=23;var U=6,T=10,R=15,O=21;s=J(s);C=e(s);Y=1732584193;X=4023233417;W=2562383102;V=271733878;for(P=0;P<C.length;P+=16){h=Y;E=X;v=W;g=V;Y=u(Y,X,W,V,C[P+0],S,3614090360);V=u(V,Y,X,W,C[P+1],Q,3905402710);W=u(W,V,Y,X,C[P+2],N,606105819);X=u(X,W,V,Y,C[P+3],M,3250441966);Y=u(Y,X,W,V,C[P+4],S,4118548399);V=u(V,Y,X,W,C[P+5],Q,1200080426);W=u(W,V,Y,X,C[P+6],N,2821735955);X=u(X,W,V,Y,C[P+7],M,4249261313);Y=u(Y,X,W,V,C[P+8],S,1770035416);V=u(V,Y,X,W,C[P+9],Q,2336552879);W=u(W,V,Y,X,C[P+10],N,4294925233);X=u(X,W,V,Y,C[P+11],M,2304563134);Y=u(Y,X,W,V,C[P+12],S,1804603682);V=u(V,Y,X,W,C[P+13],Q,4254626195);W=u(W,V,Y,X,C[P+14],N,2792965006);X=u(X,W,V,Y,C[P+15],M,1236535329);Y=f(Y,X,W,V,C[P+1],A,4129170786);V=f(V,Y,X,W,C[P+6],z,3225465664);W=f(W,V,Y,X,C[P+11],y,643717713);X=f(X,W,V,Y,C[P+0],w,3921069994);Y=f(Y,X,W,V,C[P+5],A,3593408605);V=f(V,Y,X,W,C[P+10],z,38016083);W=f(W,V,Y,X,C[P+15],y,3634488961);X=f(X,W,V,Y,C[P+4],w,3889429448);Y=f(Y,X,W,V,C[P+9],A,568446438);V=f(V,Y,X,W,C[P+14],z,3275163606);W=f(W,V,Y,X,C[P+3],y,4107603335);X=f(X,W,V,Y,C[P+8],w,1163531501);Y=f(Y,X,W,V,C[P+13],A,2850285829);V=f(V,Y,X,W,C[P+2],z,4243563512);W=f(W,V,Y,X,C[P+7],y,1735328473);X=f(X,W,V,Y,C[P+12],w,2368359562);Y=D(Y,X,W,V,C[P+5],o,4294588738);V=D(V,Y,X,W,C[P+8],m,2272392833);W=D(W,V,Y,X,C[P+11],l,1839030562);X=D(X,W,V,Y,C[P+14],j,4259657740);Y=D(Y,X,W,V,C[P+1],o,2763975236);V=D(V,Y,X,W,C[P+4],m,1272893353);W=D(W,V,Y,X,C[P+7],l,4139469664);X=D(X,W,V,Y,C[P+10],j,3200236656);Y=D(Y,X,W,V,C[P+13],o,681279174);V=D(V,Y,X,W,C[P+0],m,3936430074);W=D(W,V,Y,X,C[P+3],l,3572445317);X=D(X,W,V,Y,C[P+6],j,76029189);Y=D(Y,X,W,V,C[P+9],o,3654602809);V=D(V,Y,X,W,C[P+12],m,3873151461);W=D(W,V,Y,X,C[P+15],l,530742520);X=D(X,W,V,Y,C[P+2],j,3299628645);Y=t(Y,X,W,V,C[P+0],U,4096336452);V=t(V,Y,X,W,C[P+7],T,1126891415);W=t(W,V,Y,X,C[P+14],R,2878612391);X=t(X,W,V,Y,C[P+5],O,4237533241);Y=t(Y,X,W,V,C[P+12],U,1700485571);V=t(V,Y,X,W,C[P+3],T,2399980690);W=t(W,V,Y,X,C[P+10],R,4293915773);X=t(X,W,V,Y,C[P+1],O,2240044497);Y=t(Y,X,W,V,C[P+8],U,1873313359);V=t(V,Y,X,W,C[P+15],T,4264355552);W=t(W,V,Y,X,C[P+6],R,2734768916);X=t(X,W,V,Y,C[P+13],O,1309151649);Y=t(Y,X,W,V,C[P+4],U,4149444226);V=t(V,Y,X,W,C[P+11],T,3174756917);W=t(W,V,Y,X,C[P+2],R,718787259);X=t(X,W,V,Y,C[P+9],O,3951481745);Y=K(Y,h);X=K(X,E);W=K(W,v);V=K(V,g)}var i=B(Y)+B(X)+B(W)+B(V);return i.toLowerCase()};

var jsonval = JSON.parse(change_event);
var i=0;
for(i=0;i<jsonval.length;i++)
{
    if(jsonval[i].oldValue != null)
        {
            if(jsonval[i].oldValue.addressLine1 != null)
                {
                    if(typeof(jsonval[i].oldValue.addressLine1) !="undefined")
                        {
                            jsonval[i].oldValue.addressLine1 = MD5(jsonval[i].oldValue.addressLine1);
                        }
                }
            if(jsonval[i].oldValue.addressLine2 != null)
                {
                    if(typeof(jsonval[i].oldValue.addressLine2) !="undefined")
                        {
                            jsonval[i].oldValue.addressLine2 = MD5(jsonval[i].oldValue.addressLine2);
                        }
                }
            if(jsonval[i].oldValue.city != null)
                {                                              
                    if(typeof(jsonval[i].oldValue.city) !="undefined")
                        {
                            jsonval[i].oldValue.city = MD5(jsonval[i].oldValue.city);
                        }
                }
            if(jsonval[i].oldValue.stateProvince != null)
                {              
                    if(typeof(jsonval[i].oldValue.stateProvince) !="undefined")
                        {
                            jsonval[i].oldValue.stateProvince = MD5(jsonval[i].oldValue.stateProvince);
                        }
                }
            if(jsonval[i].oldValue.zipPostalCode != null)
                {              
                    if(typeof(jsonval[i].oldValue.zipPostalCode) !="undefined")
                        {
                            jsonval[i].oldValue.zipPostalCode = MD5(jsonval[i].oldValue.zipPostalCode);
                        }
                } 
            if(jsonval[i].oldValue.country != null)
                {              
                    if(typeof(jsonval[i].oldValue.country) !="undefined")
                        {
                            jsonval[i].oldValue.country = MD5(jsonval[i].oldValue.country);
                        }
                }
        }               

    if(jsonval[i].newValue != null)
        {
            if(jsonval[i].newValue.addressLine1 != null)
                {
                    if(typeof(jsonval[i].newValue.addressLine1) !="undefined")
                        {
                            jsonval[i].newValue.addressLine1 = MD5(jsonval[i].newValue.addressLine1);
                        }
                }
            if(jsonval[i].newValue.addressLine2 != null)
                {
                    if(typeof(jsonval[i].newValue.addressLine2) !="undefined")
                        {
                            jsonval[i].newValue.addressLine2 = MD5(jsonval[i].newValue.addressLine2);
                        }
                }
            if(jsonval[i].newValue.city != null)
                {
                    if(typeof(jsonval[i].newValue.city) !="undefined")
                        {
                            jsonval[i].newValue.city = MD5(jsonval[i].newValue.city);
                        }
                }
            if(jsonval[i].newValue.stateProvince != null)
                {
                    if(typeof(jsonval[i].newValue.stateProvince) !="undefined")
                        {
                            jsonval[i].newValue.stateProvince = MD5(jsonval[i].newValue.stateProvince);
                        }
                }
            if(jsonval[i].newValue.zipPostalCode != null)
                {
                    if(typeof(jsonval[i].newValue.zipPostalCode) !="undefined")
                        {
                            jsonval[i].newValue.zipPostalCode = MD5(jsonval[i].newValue.zipPostalCode);
                        }
                }
            if(jsonval[i].newValue.country != null)
                {
                    if(typeof(jsonval[i].newValue.country) !="undefined")
                        {
                            jsonval[i].newValue.country = MD5(jsonval[i].newValue.country);
                        }
                }

        } 

    if(jsonval[i].fieldName == "email")
            {
                  if(jsonval[i].oldValue != null) 
                  if(jsonval[i].newValue != null)    
                        {                         
                              if(typeof(jsonval[i].oldValue) !="undefined")
                              if(typeof(jsonval[i].newValue) !="undefined")
                                    {
                                          {
                                                jsonval[i].oldValue = MD5(jsonval[i].oldValue);
                                                jsonval[i].newValue = MD5(jsonval[i].newValue);
                                          }
                                    }
                        }
            }

    if(jsonval[i].fieldName == "firstName")
            {
                  if(jsonval[i].oldValue != null) 
                  if(jsonval[i].newValue != null)    
                        {                         
                              if(typeof(jsonval[i].oldValue) !="undefined")
                              if(typeof(jsonval[i].newValue) !="undefined")
                                    {
                                          {
                                                jsonval[i].oldValue = MD5(jsonval[i].oldValue);
                                                jsonval[i].newValue = MD5(jsonval[i].newValue);
                                          }
                                    }
                        }
            }

    if(jsonval[i].fieldName == "lastName")
            {
                  if(jsonval[i].oldValue != null) 
                  if(jsonval[i].newValue != null)    
                        {                         
                              if(typeof(jsonval[i].oldValue) !="undefined")
                              if(typeof(jsonval[i].newValue) !="undefined")
                                    {
                                          {
                                                jsonval[i].oldValue = MD5(jsonval[i].oldValue);
                                                jsonval[i].newValue = MD5(jsonval[i].newValue);
                                          }
                                    }
                        }
            }

    if(jsonval[i].fieldName == "MOBILE_NUMBER")
            {
                  if(jsonval[i].oldValue != null) 
                  if(jsonval[i].newValue != null)    
                        {                         
                              if(typeof(jsonval[i].oldValue) !="undefined")
                              if(typeof(jsonval[i].newValue) !="undefined")
                                    {
                                          {
                                                jsonval[i].oldValue = MD5(jsonval[i].oldValue);
                                                jsonval[i].newValue = MD5(jsonval[i].newValue);
                                          }
                                    }
                        }
            }

    if(jsonval[i].fieldName == "phoneNumber")
            {
                  if(jsonval[i].oldValue != null) 
                  if(jsonval[i].newValue != null)    
                        {                         
                              if(typeof(jsonval[i].oldValue) !="undefined")
                              if(typeof(jsonval[i].newValue) !="undefined")
                                    {
                                          {
                                                jsonval[i].oldValue = MD5(jsonval[i].oldValue);
                                                jsonval[i].newValue = MD5(jsonval[i].newValue);
                                          }
                                    }
                        }
            }

    if(jsonval[i].fieldName == "SALUTATION")
            {
                  if(jsonval[i].oldValue != null) 
                  if(jsonval[i].newValue != null)    
                        {                         
                              if(typeof(jsonval[i].oldValue) !="undefined")
                              if(typeof(jsonval[i].newValue) !="undefined")
                                    {
                                          {
                                                jsonval[i].oldValue = MD5(jsonval[i].oldValue);
                                                jsonval[i].newValue = MD5(jsonval[i].newValue);
                                          }
                                    }
                        }
            }

}
var out = JSON.stringify(jsonval);

How to mask selected values in a json field - Postgresql 9.3 and PDI

2 Answers2