Summary

I encountered an interesting problem while working on an app with a JSON function. To remove the clutter of the complexities of the app, I will explain the need and problem in a simple scenario. Later I added the solution I used to resolve the issue.

Two needs of the app were:

#1: To get table information from the app as a JSON string.

// Real application the JSON will looked diffrent and large FHIR bundle objects.
// For the simplicity, the table may look like the following as JSON.
Set(
    SampleTableJSONStr,
    JSON(
        [
            {
                name: "Homer Simpson",
                age: 61,
                secretIdentity: "CoolDad"
            },
            {
                name: "Marj Simpson",
                age: 60,
                secretIdentity: "SuperMom"
            }
        ]
    )
);

#2: Pass the above to an API in JSON format with the request as shown below.

// The above string SampleTableJSONStr must be passed as the JSON with escape quotes
{
    sourceType: "inline",
    value:       SampleTableJSONStr
}

Problem

I used the following method to pass the request to API. But the API failed with an error. The error was the “value” passed in was not a well-formatted JSON object.

// *** First time, JSON was used for search-replace some hard-coded values.
Set(
       FHIRStr,
       JSON(SampleTableJSONStr)
);

// *** Second time, JSON was used to create the payload.
Set(
      APIPayLoad,
      JSON (
          {
              sourceType: "inline,"
              value:            FHIRStr
           }
       )
);

// The APIPayLoad was passed to the API request.

Output of the First time JSON use:

You will notice the escape characters for the double quote are added as \”. That is all great, but using the same string if you call the JSON function, adds more escaped characters.

Output of the Second time JSON use:

You will notice that the additional escaping happened from the previous call. You will see the backslash character is escaped, which makes the JSON string invalid.

Solution:

As you can see, the final string will add multiple escape characters using JSON numerous times. In the above problem, I had to fix the problem by substituting the escape characters’ values. Use the Substitute function.

// FIXED code.
// *** First time, JSON was used for search-replace some hard-coded values.
// replace \" with "
// for adding quote in the string literal, you need to add two quotes ("").
Set(
    FHIRStr,
    Substitute(
        JSON(SampleTableJSONStr),
        "\""", // i.e. \"
        """"   // i.e. "
    )
);

// *** Second time, JSON was used to create the payload.
// The JSON here is adding escape characters for beginning and end of the string.
// replace "\" with "  AND
// replace \"" with "
Set(
    APIPayLoad,
    Substitute(
        Substitute(
            JSON (
                {
                    sourceType: "inline",
                    value:       FHIRStr
                }
            ),
            """\""", // i.e. "\" 
            """"     // i.e. "
        ),
        "\""""", // i.e. \""
        """"     // i.e. "
    )
);

(FIXED) Output of the First time JSON use:

As you can see, the final string does not have escape characters added to the quotes.

(FIXED) Output of the Second time JSON use:

As you can see, the final “value” property has the correct JSON escaped string.

Conclusion

Some simple things can take a long time to debug. I was trying to find out why the API we were using was not responding correctly with a valid response. It always failed that the “value” property does not have the correct JSON string.

For your information, the API I was trying to call with the abovementioned request is for Azure Health Insights, located here.

Please refer to my other blog article. The trick I used was for the same project.
How to create a JSON String array in PowerApps?