Required JSON Data
Recently, I’ve discussed how you can build flexibility into your flows if the JSON data returned by an API contains a null or an empty string. Today I’ll look at how you can validate that the API has returned all the required data.
Example Flow
Below is a typical flow that generates and processes a JSON. It is the same flow that I have used in my two previous articles.
When an email is received, the flow connects to an API using the HTTP connector. The returned JSON is reduced down to just the required content by the 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.
However, while most of the objects returned by this particular API have a complete set of data, occasionally some do not.
If any of the data is missing, then rather than load the incomplete dataset into Dataverse, I want the flow to instead initiate a manual process so the missing data can be identified.
Depending on the API, missing JSON data may take the form of an empty string, a null or the key/value pair not being returned at all. This technique will handle all of those possibilities.
Required Data
Below is the data I require from the API:
Registration Number
Company Name
Registration Date
Company Status
Company Address
Registered Address
If any of the above items are missing, then a manual process should be initiated by the sending of an email.
Ensuring Only Complete Records Are Parsed
If I only want the Parse JSON action to process objects that have the complete dataset that includes all the six key/values pairs, I can specify this using ‘required’ in the JSON schema used in the Parse JSON action, shown below:
{
"type": "array",
"items": {
"type": "object",
"properties": {
"[Registration Number]": {
"type": "number"
},
"[Company Name]": {
"type": "string"
},
"[Registration Year]": {
"type": "integer"
},
"[Company Status]": {
"type": "string"
},
"[Company Address]": {
"type": "string"
},
"[Registered Address]": {
"type": "string"
}
},
"required": [
"[Registration Number]",
"[Company Name]",
"[Registration Date] ",
"[Company Status]",
"[Company Address]",
"[Registered Address]"
]
}
} Adding A Row To Dataverse When All Required Data Is Present
When parsing, the Parse JSON action checks that all required items are present, if any key/value pairs are missing, or if any contain a null, the Parse JSON action will fail. For details on why a null causes the a failure, please see my recent post: Handling Incomplete JSON Data.
The ‘Add a new row’ to Dataverse action follows the Parse JSON action in the flow. I only want this action to execute when the Parse JSON action is successful meaning all the required data is present. I do this by setting the ‘Run after’ parameter to ‘Is successful’.
Initiating A Manual Process If Required Data Is Missing
If the Parse JSON action fails, the ‘Add a new row’ action is skipped. When this happens, I want to initiate the manual data gathering process by the sending of an email. This is done by inserting a ‘Send an email’ action, that only runs if the ‘Add a new row’ action is skipped.
Replacing Empty JSON Strings With Null
One further action may be required. If the API returns an empty string when data is missing, the empty string will need to be replaced with null. In my previous post Handling Empty JSON Strings, I explained that when a string is expected by the Parse JSON action, empty strings are acceptable and successfully parsed. In order for the Parse JSON action to fail, empty strings will need to be replaced with null before parsing. This is done by adding a Compose action (called Compose 1 in the diagram below) before the Parse JSON action with the following content.
Replace(outputs('Compose'),'""',null) 
It is likely that if no data is returned for other data types such as numbers and Booleans, an empty string will be returned for these too, but that isn’t always the case. The API documentation should provide guidance on this.
The Completed Flow
The revised flow is shown below
Now when all required data is present, the data is added to Dataverse.
When the data in the object is incomplete, the Parse JSON action fails and no data is added to Dataverse. Instead, the ‘Send an email’ action runs to initiate the manual data collection process.
There are other ways the same outcome could be achieved without specifying required items in the Parse JSON schema, such as using a Condition action following the Parse JSON action, but this would be more complicated, particularly if the API is returning a lot of key/value pairs. Specifying the required content in the JSON schema is the way to go.
Summary
Marking fields as required in your JSON schema is an excellent way to enforce data integrity. By defining which fields are required, you can prevent incomplete data from being processed and trigger alternative remedial actions.
Each API is different and checking the documentation will provide clarification. Knowing what to expect from your API will allow the above process to be tweaked to meet your particular needs.
Note: If the API was returning multiple objects, I’d need to introduce an ‘For each’ loop to scroll through the objects so each is parsed individually.
Working with JSON files is not always straightforward. Here are some recent articles to help understand how to use JSON with Power Automate.
• Simplify JSON Content Before Parsing
• Navigating a JSON Structure
Happy parsing!