Incomplete JSON Data
Another day on my JSON journey. Today I’ll explain how you can build flexibility into your flows by improving the format of a JSON schema to handle incomplete data, in particular when an API returns a null value.
Example Flow
Below is an example flow that generates and processes a JSON. When an email is received, a HTTP call is made to an API and a JSON returned. This reduced to just the content I require by use of the Compose action – see my previous 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, I have a problem. The data returned by the API is not always complete. Sometimes I receive a full set of data. Other times only part of the expected data is returned.
Below are 2 returned objects. The first has a complete set of data but the second returns a null 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]": null,
"[Company Status]": "null,
"[Company Address]": "666 Business St, City",
"[Registered Address]": "777 Office Rd, City"
} The presence of nulls may be a problem. If nulls are not explicitly allowed in the Parse JSON schema then an error will be generated.
When you expeience this for the first time, understanding why the error occured and addressing it can be challenging. It’s possible that 99 times out of 100, all the data is returned and each time the flow completes, but on the 100th time (typically after the flow is made live!) a null is returned and the flow fails. Don’t rely on trial and error, it’s is useful to check to check the API documentation (if available) to see if a null may be returned.
Allow Nulls in the Parse JSON Schema
When building the flow, before creating the Parse JSON action, I typically run the flow to get a sample output of the HTTP action, which I copy. I then create the Parse JSON action and set its content to the output of the preceding HTTP action and paste the schema into the Parse JSON action as ‘Generate from sample’. Once done, I can see the schema I have just created in the parse JSON action.
Here is the full JSON schema:
{
"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]"
]
}
} If I Parse the output from the HTTP action using the above schema, that will work fine when the data for a particular key/value pair is returned by the API, but if a null is returned, then an error will be thrown.
To address this, I need to convert the Parse JSON schema to also accept null values and I do this by changing the key/value pairs to ‘union types’ in the format below:
"[Company Name]": {
"type": ["string", "null"]
} In the amended schema below. Registration Number cannot be null, as it is the unique key, but all the other fields can have a string value or null.:
{
"type": "array",
"items": {
"type": "object",
"properties": {
"[Registration Number]": {
"type": "number"
},
"[Company Name]": {
"type": ["string", "null"]
},
"[Registration Year]": {
"type": ["integer", "null"]
},
"[Company Status]": {
"type": ["string", "null"]
},
"[Company Address]": {
"type": ["string", "null"]
},
"[Registered Address]": {
"type": ["string", "null"]
}
},
"required": [
"[Registration Number]"
]
}
} Now when I run the flow, it completes successfully. All the data is added to Dataverse, including nulls, and no errors are generated.
This approach works with all other JSON data types too, such as boolean and number. Just ensure that if you are adding the data to Dataverse, as I am, or to another data store, that nulls can be accepted.
Summary
If you suspect that nulls may be returned by your API, chsnge the Parse JSON schema key/value pairs so they accept a null value. This way, irrespective of whether the API returns data or null, the Parse JSON action will complete successfully. Also ensure that the data store where you are adding the parsed data accepts nulls.
Should you need it, 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 parsIing