We help IT Professionals succeed at work.

Nested replace of strings with double quotes in Javascript

AlHal2
AlHal2 used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
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?
NorieAnalyst Assistant

Commented:
AlHal2

Do you mean you have multiple 'Payload' elements?

Or do you mean the elements/fields like 'PR_RENT' etc.?
leakim971Multitechnician
Top Expert 2014

Commented:
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


Capture-d-e-cran-2019-12-05-a--06.47.png
Paste your JSON here : http://jsonviewer.stack.hu/

Capture-d-e-cran-2019-12-05-a--06.48.png

Author

Commented:
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'.
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

NorieAnalyst Assistant

Commented:
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

Author

Commented:
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;
NorieAnalyst Assistant

Commented:
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

Author

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

Commented:
Is there any output at all?
Commented:
This worked.  Perhaps there is something specific about Logic Apps.
var json = workflowContext.actions.HTTP_2.outputs.body;
json=json.replace(/"PR_USER8":""/g,'"PR_USER8":0').replace(/"PR_RENT":""/g,'"PR_RENT":0').replace(/"PR_ID":""/g,'"PR_ID":0').replace(/"PR_USUM":""/g,'"PR_USUM":0').replace(/"PR_LEAS":""/g,'"PR_LEAS":0');
return json;

Open in new window

Author

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

Author

Commented:
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