Unable to replace double qoutes (") in Create/Update Account JSON for REST based Connector

[Auvik_Dey]

Hi Team,

We have been receiving user records with double quotes(") in few of the usercustom property. Custom property7 is used for provisioning data to Service Now system and we are unable to replace the double quotes within the custom property value provided by our authorative source(SAP).

For example, if CP7 is sent by SAP like Sanah "Gcasot" ranjan,  we want to provision it to Service now application as Sanah Gcasot ranjan.

I have tried the following alternatives in the JSON and none of them worked:

1. \"${user.customproperty7.replaceAll(\"\"\",\"\")}\" (This is currently present in the attached file)
2. \"${user.customproperty7.replaceAll(\"\"\",'')}\"
3. \"u_preferred_name\":\"${user.customproperty7.replaceAll('\"','')}\"
4. \"u_preferred_name\":\"${user.customproperty7.replaceAll('\\\"','')}\"
5. \"u_preferred_name\":\"${user.customproperty7.replaceAll('\\\\\"','')}\"
6. \"u_preferred_name\":\"${user.customproperty7.replaceAll('\\\\\\\"','')}\"
7. \"u_preferred_name\":\"${user.customproperty7.replaceAll('[\"]','')}\"

Can anyone suggest how can i escape the doublequotes(") in a rest connector JSON

regards,

Auvik

[sk]

try below

\"${user.customproperty7.replaceAll('\"','')}\"

\"${user.customproperty7.replaceAll('\\"','')}\"

\"${user.customproperty7.replaceAll('\\\\"','')}\"

[Auvik_Dey]

Hi,

I checked the ones provided by you, those did not work as even number of \ is giving JSON validator error.

The single \ was not working, however, I am able to escape with 7-\ like as mentioned below:
 \"u_preferred_name\":\"${user.customproperty7.replaceAll('\\\\\\\"','')}\" is working now.

[adriencosson]

Hi @Auvik_Dey,

Glad to know your solution is working.

Quick suggestion, if you want to get rid of the double quotes directly when receiving HR data, you can leverage the MODIFYUSERDATAJSON parameter of the authoritative source connector, using a sample below that removes the double quotes on custom property 7. This way, you would simply use ${user.customproperty7} in all your provisioning connectors.

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT customproperty7 FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "statuskey"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA SET customproperty7 = REPLACE(customproperty7, '\"', '')"
  ]
}

 Hope this helps !