Empty JSON Strings
When APIs have no data, generally either a null value or an empty string is returned. Last week in my post Handling Incomplete JSON Data, I discussed how to handle null values. Today I’ll explain how to accomodate empty strings.
Example Flow
Here is the example flow that I used last week. It generates and processes a JSON. When an email is received, a HTTP call is made to an API and a JSON returned. The JSON is reduced to just the content I require by use of the Compose action – see my recent post Simplify JSON Content Before Parsing for details on how to do this. The simplified JSON is subsequently parsed by the Parse JSON action, and the output is entered into Dataverse.
Below are 2 returned objects. The first has a complete set of data but the second returns an empty string for Registration Year and Company Status.
{
"[Registration Number]": 12345,
"[Company Name]": "ABC Ltd",
"[Registration Year]": 2020,
"[Company Status]": "Active",
"[Company Address]": "123 Business St, City",
"[Registered Address]": "456 Office Rd, City"
} {
"[Registration Number]": 67890,
"[Company Name]": "XYZ Ltd",
"[Registration Year]": "",
"[Company Status]": "",
"[Company Address]": "666 Business St, City",
"[Registered Address]": "777 Office Rd, City"
} The presence of empty strings is not a problem for the Parse JSON action. It is expecting a string and it receives a string. The fact the string is empty doesn’t matter and the Parsing will successfully complete.
However, when the flow attempts to load the data into Dataverse, an error is generated bacause Dataverse won’t accept an empty string. However, as we saw last week, it will accept null. So we have to replace all empty strings with null before running the ‘Add a new row’ to Dataverse action,
Replacing Empty Strings With Null
To replace empty strings with null, use the OData Coalesce function. Coalesce has many uses and has two parameters. If the first parameter exists, then it is used. If the first paramater doesn’t exist (such as an empty string), then the second parameter is used instead. So in our particular case, if a value exists in a particular key/value pair JSON object it is used, but an empty string is replace by null.
Instead of entering the direct output from the Parse JSON action into Dataverse ‘Add a new row’ action, use the Coalesce expression below:
coalesce(body()?['[Company Name]'], null)
Note: The reason that the above formula works is due to the optional chaining operator ‘?’. Including this operator is essential because it acts as a “safe navigator.” It checks if a property exists before attempting to access it, and if the property is an empty string or null, it skips it rather than throwing an error. If the chaining operator is omitted, the first parameter will always be used. When creating an expression in Power Automate the chaining operator is automatically added.
As an alternative approach, instead of adding the formula directly into Dataverse, it can be used in a Select action, as below. The output of the Selection action is then used in the ‘Add a new row’ Dataverse action.
Other Content Types
If the JSON object returned by the API has numbers, booleans, or other content types, it’s worth checking what these contain if there is no data. It could be an empty string or something different. Whatever is returned, make sure that your Parse JSON schema can handle it and that the content can be successfully added to Dataverse. The API documentation should help with this.
Summary
Processing JSONs returned by APIs can be tricky. You can test the flow many times, but there may be an occasion where an empty string is returned. Sometimes this possibilty is made clear in the API documentation and sometimes not.
Unless you are certain that empty strings won’t be returned, I would always recommend using the Coalese function when adding data to Dataverse. Better safe than sorry. You can also allow for nulls being returned as in my previous post Handling Incomplete JSON Data.
Here are a couple of other blog posts on using JSON with Power Automate.
• Simplify JSON Content Before Parsing
• Navigating a JSON Structure
Feel free to add any thoughts comments, or JSON parsing tips of your own below.