Pankaj Surti's Blog

Summary

In Power Apps, creating a JSON object is easy. It can be initialized like this.

// Created an object with the types as array of strings
Set (
    anObject,
    {
        service: "Food",
        types: [
            "Bread",
            "Pasta",
            "Soup"
        ]
    }
);

We can use the JSON function to convert the above object to a string. It will give you the JSON string representation to pass to the API as a payload. (Note: I am simplifying the API you may have. It may be complex, but this example will give you an idea.)

Set (
    aJSONStr,
    JSON(anObject)
);

The output of the above call will be the following. Note that the “Value” is added to the string array ‘types’. It will not be a valid payload.

“{\”service\”:\”Food\”,\”types\”:[{\”Value\”:\”Bread\”},{\”Value\”:\”Pasta\”},{\”Value\”:\”Soup\”}]}”

The valid output of the payload string should be:

“{\”service\”:\”Food\”,\”types\”:[\”Bread\”,”Pasta\”,”Soup\”]}”

So, how can we achieve that?

Solution:

So, in Power Apps, that is the expected behavior for the array of strings to add the “Value” property.

The following solution is to replace the highlighted and stricken characters with a blank for every occurrence of the match found.

“{\”service\”:\”Food\”,\”types\”:[ {\”Value\”: \”Bread\” } , {\”Value\”: \”Pasta\” } ,{\”Value\”: \”Soup\” } ]}”

Utilized the Look Ahead Regular Expression technique.

Set (
    aValidJSONStr,
    With (
        {
            InnerWorkStr: JSON(
                anObject,
                JSONFormat.Compact
            )
        },
        With (
            {
                matches: MatchAll(
                    InnerWorkStr,
                    "\{""Value"":(?=.*\})"
                )
            },
            Collect(
                labelTextColl,
                InnerWorkStr
            );
            ForAll(
                matches,
                Collect(
                    labelTextColl,
                    // Find and replace end curly bracket of {\"Value\":\"Bread\"}
                    Replace(
                        // substitute 9 spaces for the string match {\"Value\":
                        Substitute(
                            Last(labelTextColl).Value,
                            ThisRecord.FullMatch,
                            "         "
                        ),
                        Find(
                            "}",
                            Last(labelTextColl).Value,
                            ThisRecord.StartMatch
                        ),
                        1,
                        " "
                    )
                );
            );
            Last(labelTextColl).Value;
            // Very Important as the results returned
        )// End of inner With
    )// End of outer With
);

After executing the above search and replace logic using the logical expression, you will get the valid JSON string with the correct string array as expected for the API payload.

Conclusion

I hope you find this useful for your project as I had to figure this out for my project, and I spent some time understanding the problem and coming up with the solution.

Please also refer to my previous posts related to this post.

How to create a JSON String array in PowerApps?

How to find and replace an array of strings from a large string variable using PowerFx?

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?