asked on

Nested replace of strings with double quotes in Javascript

In Logic Apps I have this function which replaces double double quotes with a zero for some strings. How can I replicate this in Javascript?

replace(replace(replace(replace(replace(body('HTTP_2'),'"PR_RENT":""','"PR_RENT":0'),'"PR_ID":""','"PR_ID":0'),'"PR_USUM":""','"PR_USUM":0'),'"PR_LEAS":""','"PR_LEAS":0'),'"PR_USER8":""','"PR_USER8":0')

Open in new window

I tried this code, but it is not working. The first line is because I'm using the javascript in Logic Apps.

var json = workflowContext.actions.HTTP_2.outputs.body
if (json.PR_RENT=="\"\"")
{
json.PR_RENT=0
}
if (json.PR_ID=="\"\"")
{
json.PR_ID=0
}
if (json.PR_USUM=="\"\"")
{
json.PR_USUM=0
}
if (json.PR_LEAS=="\"\"")
{
json.PR_LEAS=0
}
if (json.PR_USER8=="\"\"")
{
json.PR_USER8=0
}
return json;

Open in new window

Here is some of my json code.

{"Payload":[{"RLS_GROUP":"","PR_SNAM":"700063","PR_OWN":"qqq","PR_REF":"","PR_NAME":"Bqqq12","PR_ADD1":"qqq","PR_ADD2":"INDUSTRIAL ESTATE","PR_ADD3":"23  INDUSTRIAL ESTATE","PR_ADD4":"yyy","PR_ADD5":"","PR_ADD6":"GB","PR_POST":"WQDQWD","PR_TEL":"23213","PR_TELX":"21312312","PR_FAX":"","PR_CONT":"","PR_NUNIT":"","PR_INT":"","PR_TENR":"LEASED","PR_QDAY":"","PR_CLSS":"","PR_DRCT":"Closing","PR_AGENT":"","PR_NOWN":"","PR_BOWN":"","PR_SOL":"","PR_HSTT":"","PR_HEND":"","PR_HAMT":"","PR_PFREQ":"","PR_NTENT":"","PR_NFLR":"","PR_GRA":"","PR_WATER":"","PR_RATVAL":"","PR_RTCT":"","PR_SCHG":"","PR_OCHG":"","PR_GFA":"","PR_ZONEA":"","PR_ZONEB":"","PR_ZONEC":"","PR_UPDATE":"","PR_UTIME":"","PR_UUSER":"","PR_HIST":"","PR_TAXYN":"","PR_TAX":"","PR_START":"","PR_END":"","PR_FREQ":"","PR_QTR":"","PR_NDUE":"","PR_TAXRUN":"","PR_OUTLET":"","PR_INLET":"","PR_VAL":"","PR_CST":"","PR_FRWA":"","PR_FRWB":"","PR_PRINT":"","PR_NL":"","PR_CURRS":"","PR_NEXTS":"","PR_VAT":"D","PR_USER":"","PR_VQDAY":"","PR_OBS1":"STANDARD NORTH","PR_TYPE":"Property","PR_VATDATE":"","PR_FUTHER":"","PR_RESTEN":"","PR_CAPGOODS":"","PR_INSEE":"","PR_CURR":"","PR_AQDATE":"20190917","PR_USER1":"Office","PR_USER2":"Yes","PR_USER3":"","PR_USER4":"","PR_USER5":"20190917","PR_USER6":"","PR_USER7":"","PR_USER8":0,"PR_USER9":"","PR_USER10":"","PR_OBS2":"","PR_OBS3":"","PR_OBS4":"","PR_OBS5":"","PR_OBS6":"","PR_OBS7":"UK","PR_OBS8":"","PR_OBS9":"","PR_SOLD":"0","PR_DATESOLD":"20200917","PR_LAND":"","PR_LANDUM":"","PR_FREE":"","PR_ID":0,"PR_BTYP":"F","PR_LEAS":0,"PR_RENT":999999,"PR_USUM":0,"PR_FBUI":0,"PR_DREN":"","PR_USRC":"","PR_RSRC":"","PR_LSRC":"","PR_ELSR":"","PR_EGRS":"","PR_PROR":"","PR_BSTA":"","PR_LNAM":"123123213","PR_SITE":"","PR_REGION":"","PR_DESC":""}]}

Open in new window

I have to do this because the API is returning "" for integer fields instead of zero when there is no value.

Mrunal

Hi Al,
Instead of replacing double quot with 0 I'll prefer to manipulate JSON in Javascript function and check values.
If value is blank then you can update to 0(zero) and use updated JSON object.
Here is sample code:

var json = { "Payload": [{ "RLS_GROUP": "", "PR_SNAM": "700063", "PR_OWN": "qqq", "PR_REF": "", "PR_NAME": "Bqqq12", "PR_ADD1": "qqq", "PR_ADD2": "INDUSTRIAL ESTATE", "PR_ADD3": "23  INDUSTRIAL ESTATE", "PR_ADD4": "yyy", "PR_ADD5": "", "PR_ADD6": "GB", "PR_POST": "WQDQWD", "PR_TEL": "23213", "PR_TELX": "21312312", "PR_FAX": "", "PR_CONT": "", "PR_NUNIT": "", "PR_INT": "", "PR_TENR": "LEASED", "PR_QDAY": "", "PR_CLSS": "", "PR_DRCT": "Closing", "PR_AGENT": "", "PR_NOWN": "", "PR_BOWN": "", "PR_SOL": "", "PR_HSTT": "", "PR_HEND": "", "PR_HAMT": "", "PR_PFREQ": "", "PR_NTENT": "", "PR_NFLR": "", "PR_GRA": "", "PR_WATER": "", "PR_RATVAL": "", "PR_RTCT": "", "PR_SCHG": "", "PR_OCHG": "", "PR_GFA": "", "PR_ZONEA": "", "PR_ZONEB": "", "PR_ZONEC": "", "PR_UPDATE": "", "PR_UTIME": "", "PR_UUSER": "", "PR_HIST": "", "PR_TAXYN": "", "PR_TAX": "", "PR_START": "", "PR_END": "", "PR_FREQ": "", "PR_QTR": "", "PR_NDUE": "", "PR_TAXRUN": "", "PR_OUTLET": "", "PR_INLET": "", "PR_VAL": "", "PR_CST": "", "PR_FRWA": "", "PR_FRWB": "", "PR_PRINT": "", "PR_NL": "", "PR_CURRS": "", "PR_NEXTS": "", "PR_VAT": "D", "PR_USER": "", "PR_VQDAY": "", "PR_OBS1": "STANDARD NORTH", "PR_TYPE": "Property", "PR_VATDATE": "", "PR_FUTHER": "", "PR_RESTEN": "", "PR_CAPGOODS": "", "PR_INSEE": "", "PR_CURR": "", "PR_AQDATE": "20190917", "PR_USER1": "Office", "PR_USER2": "Yes", "PR_USER3": "", "PR_USER4": "", "PR_USER5": "20190917", "PR_USER6": "", "PR_USER7": "", "PR_USER8": 0, "PR_USER9": "", "PR_USER10": "", "PR_OBS2": "", "PR_OBS3": "", "PR_OBS4": "", "PR_OBS5": "", "PR_OBS6": "", "PR_OBS7": "UK", "PR_OBS8": "", "PR_OBS9": "", "PR_SOLD": "0", "PR_DATESOLD": "20200917", "PR_LAND": "", "PR_LANDUM": "", "PR_FREE": "", "PR_ID": 0, "PR_BTYP": "F", "PR_LEAS": 0, "PR_RENT": 999999, "PR_USUM": 0, "PR_FBUI": 0, "PR_DREN": "", "PR_USRC": "", "PR_RSRC": "", "PR_LSRC": "", "PR_ELSR": "", "PR_EGRS": "", "PR_PROR": "", "PR_BSTA": "", "PR_LNAM": "123123213", "PR_SITE": "", "PR_REGION": "", "PR_DESC": "" }] };            
            if (json.Payload[0].PR_ADD5 == "") {
                json.Payload[0].PR_ADD5 = 0;
            }
            var newjson = json;

Open in new window

AlHal2

ASKER

The sample I give is one element. How would I iterate through the array. You have

json.Payload[0].PR_ADD5 == ""

Open in new window

If 0 represents the first element, how will it iterate through all the elements?

Norie

AlHal2

Do you mean you have multiple 'Payload' elements?

Or do you mean the elements/fields like 'PR_RENT' etc.?

leakim971

var json = workflowContext.actions.HTTP_2.outputs.body
if (json.Payload[0].PR_RENT=="\"\"") {
	json.Payload[0].PR_RENT=0;
}
if (json.Payload[0].PR_ID=="\"\"") {
	json.Payload[0].PR_ID=0;
}
if (json.Payload[0].PR_USUM=="\"\"") {
	json.Payload[0].PR_USUM=0;
}
if (json.Payload[0].PR_LEAS=="\"\"") {
	json.Payload[0].PR_LEAS=0
}
if (json.Payload[0].PR_USER8=="\"\"") {
	json.Payload[0].PR_USER8=0
}
return json;

Open in new window

Paste your JSON here : http://jsonviewer.stack.hu/

AlHal2

ASKER

Norie, I meant multiple payload elements. This is what I tried.

var json = workflowContext.actions.HTTP_2.outputs.body
for (var i = 0; i < json.length; i++) {
if (json.Payload[i].PR_RENT=="\"\"") {
	json.Payload[i].PR_RENT=0;
}
if (json.Payload[i].PR_ID=="\"\"") {
	json.Payload[i].PR_ID=0;
}
if (json.Payload[i].PR_USUM=="\"\"") {
	json.Payload[i].PR_USUM=0;
}
if (json.Payload[i].PR_LEAS=="\"\"") {
	json.Payload[i].PR_LEAS=0;
}
if (json.Payload[i].PR_USER8=="\"\"") {
	json.Payload[i].PR_USER8=0;
}
}
return json;

Open in new window

I got this error message

InlineCodeScriptRuntimeFailure. The inline code action 'JavaScriptCode' execution failed, with error 'Cannot read property '0' of undefined'.

Mrunal

Hey
you are looping with wrong list.
In your for loop - for (var i = 0; i < json.length; i++) {
json.length is undefined.

try below code:

var json = { "Payload": [{ "RLS_GROUP": "", "PR_SNAM": "700063", "PR_OWN": "qqq", "PR_REF": "", "PR_NAME": "Bqqq12", "PR_ADD1": "qqq", "PR_ADD2": "INDUSTRIAL ESTATE", "PR_ADD3": "23  INDUSTRIAL ESTATE", "PR_ADD4": "yyy", "PR_ADD5": "", "PR_ADD6": "GB", "PR_POST": "WQDQWD", "PR_TEL": "23213", "PR_TELX": "21312312", "PR_FAX": "", "PR_CONT": "", "PR_NUNIT": "", "PR_INT": "", "PR_TENR": "LEASED", "PR_QDAY": "", "PR_CLSS": "", "PR_DRCT": "Closing", "PR_AGENT": "", "PR_NOWN": "", "PR_BOWN": "", "PR_SOL": "", "PR_HSTT": "", "PR_HEND": "", "PR_HAMT": "", "PR_PFREQ": "", "PR_NTENT": "", "PR_NFLR": "", "PR_GRA": "", "PR_WATER": "", "PR_RATVAL": "", "PR_RTCT": "", "PR_SCHG": "", "PR_OCHG": "", "PR_GFA": "", "PR_ZONEA": "", "PR_ZONEB": "", "PR_ZONEC": "", "PR_UPDATE": "", "PR_UTIME": "", "PR_UUSER": "", "PR_HIST": "", "PR_TAXYN": "", "PR_TAX": "", "PR_START": "", "PR_END": "", "PR_FREQ": "", "PR_QTR": "", "PR_NDUE": "", "PR_TAXRUN": "", "PR_OUTLET": "", "PR_INLET": "", "PR_VAL": "", "PR_CST": "", "PR_FRWA": "", "PR_FRWB": "", "PR_PRINT": "", "PR_NL": "", "PR_CURRS": "", "PR_NEXTS": "", "PR_VAT": "D", "PR_USER": "", "PR_VQDAY": "", "PR_OBS1": "STANDARD NORTH", "PR_TYPE": "Property", "PR_VATDATE": "", "PR_FUTHER": "", "PR_RESTEN": "", "PR_CAPGOODS": "", "PR_INSEE": "", "PR_CURR": "", "PR_AQDATE": "20190917", "PR_USER1": "Office", "PR_USER2": "Yes", "PR_USER3": "", "PR_USER4": "", "PR_USER5": "20190917", "PR_USER6": "", "PR_USER7": "", "PR_USER8": 0, "PR_USER9": "", "PR_USER10": "", "PR_OBS2": "", "PR_OBS3": "", "PR_OBS4": "", "PR_OBS5": "", "PR_OBS6": "", "PR_OBS7": "UK", "PR_OBS8": "", "PR_OBS9": "", "PR_SOLD": "0", "PR_DATESOLD": "20200917", "PR_LAND": "", "PR_LANDUM": "", "PR_FREE": "", "PR_ID": 0, "PR_BTYP": "F", "PR_LEAS": 0, "PR_RENT": 999999, "PR_USUM": 0, "PR_FBUI": 0, "PR_DREN": "", "PR_USRC": "", "PR_RSRC": "", "PR_LSRC": "", "PR_ELSR": "", "PR_EGRS": "", "PR_PROR": "", "PR_BSTA": "", "PR_LNAM": "123123213", "PR_SITE": "", "PR_REGION": "", "PR_DESC": "" }] };            
            var payloadObj = json.Payload[0]; 
            for (var key in payloadObj) {
                if (payloadObj.PR_ADD5 == "") {
                    payloadObj.PR_ADD5 = 0;                    
                }
                //add your more conditions here
            }            
            var newjson = json;

Open in new window

Norie

AlHal2

What do you actually have in json?

Is it a string?

This code assumes it is so uses JSON.parse at the start.

const json =JSON.parse('{"Payload":[{"RLS_GROUP":"","PR_SNAM":"700063","PR_OWN":"qqq","PR_REF":"","PR_NAME":"Bqqq12","PR_ADD1":"qqq","PR_ADD2":"INDUSTRIAL ESTATE","PR_ADD3":"23  INDUSTRIAL ESTATE","PR_ADD4":"yyy","PR_ADD5":"","PR_ADD6":"GB","PR_POST":"WQDQWD","PR_TEL":"23213","PR_TELX":"21312312","PR_FAX":"","PR_CONT":"","PR_NUNIT":"","PR_INT":"","PR_TENR":"LEASED","PR_QDAY":"","PR_CLSS":"","PR_DRCT":"Closing","PR_AGENT":"","PR_NOWN":"","PR_BOWN":"","PR_SOL":"","PR_HSTT":"","PR_HEND":"","PR_HAMT":"","PR_PFREQ":"","PR_NTENT":"","PR_NFLR":"","PR_GRA":"","PR_WATER":"","PR_RATVAL":"","PR_RTCT":"","PR_SCHG":"","PR_OCHG":"","PR_GFA":"","PR_ZONEA":"","PR_ZONEB":"","PR_ZONEC":"","PR_UPDATE":"","PR_UTIME":"","PR_UUSER":"","PR_HIST":"","PR_TAXYN":"","PR_TAX":"","PR_START":"","PR_END":"","PR_FREQ":"","PR_QTR":"","PR_NDUE":"","PR_TAXRUN":"","PR_OUTLET":"","PR_INLET":"","PR_VAL":"","PR_CST":"","PR_FRWA":"","PR_FRWB":"","PR_PRINT":"","PR_NL":"","PR_CURRS":"","PR_NEXTS":"","PR_VAT":"D","PR_USER":"","PR_VQDAY":"","PR_OBS1":"STANDARD NORTH","PR_TYPE":"Property","PR_VATDATE":"","PR_FUTHER":"","PR_RESTEN":"","PR_CAPGOODS":"","PR_INSEE":"","PR_CURR":"","PR_AQDATE":"20190917","PR_USER1":"Office","PR_USER2":"Yes","PR_USER3":"","PR_USER4":"","PR_USER5":"20190917","PR_USER6":"","PR_USER7":"","PR_USER8":0,"PR_USER9":"","PR_USER10":"","PR_OBS2":"","PR_OBS3":"","PR_OBS4":"","PR_OBS5":"","PR_OBS6":"","PR_OBS7":"UK","PR_OBS8":"","PR_OBS9":"","PR_SOLD":"0","PR_DATESOLD":"20200917","PR_LAND":"","PR_LANDUM":"","PR_FREE":"","PR_ID":0,"PR_BTYP":"F","PR_LEAS":0,"PR_RENT":999999,"PR_USUM":0,"PR_FBUI":0,"PR_DREN":"","PR_USRC":"","PR_RSRC":"","PR_LSRC":"","PR_ELSR":"","PR_EGRS":"","PR_PROR":"","PR_BSTA":"","PR_LNAM":"123123213","PR_SITE":"","PR_REGION":"","PR_DESC":""}]}')

const dic = json['Payload'][0];

const checkFields = ['PR_RENT','PR_ID','PR_USUM','PR_LEAS','PR_USER8'];

checkFields.forEach(field=>{

    console.log(dic[field]);
    
    if(dic[field]=='""')
    {
        dic[field]=0;
        console.log(field);
    }
}
);

Open in new window

AlHal2

ASKER

For both code samples above I get an error of InlineCodeScriptRuntimeFailure. The inline code action 'JavaScriptCode' execution failed, with error 'Cannot read property '0' of undefined'.

My JSON is the result of an http request. The sample I give above is the first record.
The first line must be like this as I am using the inline code action of Azure Logic Apps.
var json = workflowContext.actions.HTTP_2.outputs.body;

Norie

Perhaps something like this will work, if it doesn't can you tell us where you are getting the error(s) in the code?

var jsonString = workflowContext.actions.HTTP_2.outputs.body;

const json =JSON.parse(jsonString);

const dic = json['Payload'][0];

const checkFields = ['PR_RENT','PR_ID','PR_USUM','PR_LEAS','PR_USER8'];

checkFields.forEach(field=>{

    console.log(dic[field]);
    
    if(dic[field]=='""')
    {
        dic[field]=0;
        console.log(field);
    }
}
);

Open in new window

AlHal2

ASKER

No error, but it's not changing anything. I added the line return json at the bottom.

Norie

Is there any output at all?

ASKER CERTIFIED SOLUTION

AlHal2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AlHal2

ASKER

Your code did not seem to change anything. Should it have included /g?

AlHal2

ASKER

This works better as it treats the json as an object not a string.

var jsonString = workflowContext.actions.HTTP_2.outputs.body;
const json =JSON.parse(jsonString);
const ret =[];

const dic = json['Payload'];
const checkFields = ['PR_RENT','PR_ID','PR_USUM','PR_LEAS','PR_USER8'];
for(i in json['Payload'])
{

 
checkFields.forEach(field=>{
     if(json.Payload[i][field]=="")
    {
        json.Payload[i][field]=0;
       // console.log(field);
    }
}

);
}

return json;

Open in new window