Replace ' from json String

How do I do X?
1

Hi,

i have json Data:
for example:

{
  "Data": {
    "PageSize": 1000,
    "IsLastPage": false,
    "NextPageLink": "/v1.0/api/portfolio?page=4&pagesize=1000",
    "PreviousPageLink": "/v1.0/api/portfolio?page=2&pagesize=1000",
    "CurrentPageNumber": 3,
    "TotalRows": 7839,
    "Page": [
      {
        "DId": "69c858a4-0000-4ce7-af1a-5e435ed30804",
        "CustomerReferenceNumbers": [
          "3432432434O"
        ],
        "CountryCode": "JP",
        "CountryName": "Japan",
        "Title": "Title apparatus's with double sided roof's topped",
        "IpType": "Pat",
        "IpSubType": "pat",
        "Origin": "Nat",
        "Status": "Inactived",
        "Application": {
          "Number": "2022-186354",
          "Date": "2008-07-17"
        },
        "Grant": {},
        "NationalFiling": {},
        "Publication": {},
        "ParentApplication": {},
        "ExpiryDate": "2028-07-17",
        "NumberOfClaims": 0,
        "LicenseOfRight": false,
        "CostCenters": [
          {
            "ReferenceNumber": "12345",
            "Split": 100,
            "Company": {
              "Name": "Field",
              "Number": "001"
            }
          }
        ],
        "RegisteredOwners": [
          {
            "Name": "XXXXXXXXXXXXXXX",
            "Number": ""
          }
        ]
      }
    ]
  },
  "Errors": [],
  "HasError": false
}

for Using the Data in an MSSQL Query i want to remove the " ’ ". Otherwise the Query fails, because MSSQL breaks the Query with an Syntax Error.

Can anybody give me tipp for replacing the " ’ "

thank you in advance

Franz

2

@franz can you try escaping the single quote by using a doubled single-quote character as mentioned here How do I escape a single quote in SQL Server? - Stack Overflow

3

Hi Dancia,

that’s what I want to do. But since the json is read from an API data I probably have to do that most simply via javascript in the Appsmith variable before I can use the data in the SQL query.

So I would need a javascript function that converts the ’ to ‘’ from the json string.

Franz

4

ok. then for JS you need to try using the replace() function. Check this post - javascript - How to escape double and single quotes in a JSON array - Stack Overflow You may need to modify this for the syntax MSSQL requires

5

Hi,

i tried
Get_Portfolio is an API GET Call, which results the json Data.

{{Get_Portfolio.data.replace(/'/g, '')}}

and

{{Get_Portfolio.data.replace("'", "")}}

but both is “undefined”. And says “TypeError: Get_Portfolio.data.replace is not a function”
probably not as easy as I thought .

Franz

6

Did you also try stringifying the JSON as shown in the post?

7

no, but I don’t have any idea how and where I can do that in Appsmith. Do I need a Javascript function for this or can I do it within the SQL query.

Sorry for the dummy questions.

8

Hi Franz! Please try this:

{{JSON.parse(JSON.stringify(Get_Portfolio.data).replaceAll("'", ""))}}
1 Like
9

Hi Amelia,
works great, 1000 thanks. :smiling_face_with_three_hearts:
Franz