Handling nonexistent, null and multi value type properties in Parse JSON action

In Power Automate cloud flow, Parse JSON action is used to access properties in JSON content enabling you to select those properties from the dynamic content list on your subsequent actions. Typically the JSON content will be from a response to an API call. The first step after adding the action is to select the source of the JSON content and to add the schema, you can either provide a JSON schema from your request payload or the action can generate based on a sample content. If you chose to generate the schema from a sample, the schema is generated based on the first object from the sample JSON content.

For the following sample JSON content, the property

• Name in the first element is of type string and in the second element is of type null
• EmpNo in the first element is of type integer and in the second element is of type string
• Country in the first element is of type string and in the second element it does not exist

[
  {
    "Name": "Mohamed Ashiq Faleel",
    "EmpNo": 123456,
    "Country": "Sweden"
  },
  {
    "Name": null,
    "EmpNo":  "EX-123456"
 }
]

the generated schema is

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Name": {
                "type": "string"
            },
            "EmpNo": {
                "type": "integer"
            },
            "Country": {
                "type": "string"
            }
        },
        "required": [
            "Name",
            "EmpNo"
        ]
    }
}

• The Type of the properties in the above schema is based on the property values from the first element [Name – String, EmpNo – Integer & Location – String]
• The required property only Name and EmpNo since Country is not available in the second element

In this blog post, let us see how to handle the following in Parse JSON action

1. Null and Multi value type property
2. Non Existent property

I have added the above sample content in an Array variable and the above generated schema in the Parse JSON action as shown below

Null and Multi value type property:

The sample content had elements with null property value [Name – second element] and the type of the property is different on each element [EmpNo – Integer and String]. If you run the flow, it will fail with the message ValidationFailed. The schema validation failed for the Parse JSON action and the output should have the following error in the output

• Invalid type. Expected String but got Null.
  • Reason – Property Name in the second element null and not string as defined in the schema
• Invalid type. Expected Integer but got String
  • Reason – Property EmpNo in the second element had string value and not integer as defined in the schema

There are two methods to solve this problem.

Method 1:

Add additional data types to the property Name [null] and EmpNo [string] as shown below

{
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "Name": {
        "type": [
          "string",
          "null"
        ]
      },
      "EmpNo": {
        "type": [
          "integer",
          "string"
        ]
      },
      "Country": {
        "type": "string"
      }
    },
    "required": [
      "Name",
      "EmpNo"
    ]
  }
}

The drawback with this approach is the property values will not show in dynamic content panel.

You will have to write expression to get the value

items('Apply_to_each')?['Name']
items('Apply_to_each')?['EmpNo']

Method 2 – Recommended:

Remove the type information for the property Name and EmpNo from the schema as shown below

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Name": {},
            "EmpNo": {},
            "Country": {
                "type": "string"
            }
        },
        "required": [
            "Name",
            "EmpNo"
        ]
    }
}

This way you will not loose the selection of properties from the dynamic content

Non Existent property:

The sample content did not have the property Country in the second element. The first check is to validate the required property from the schema

"required": [
            "Name",
            "EmpNo"
        ]

If you look above which is taken from the schema, Country is not there so all is good. The expression to get the value is

items('Apply_to_each')?['Country']

Find below screenshot of the run for the property Country from the second element

To have a meaningful output, you can write an expression to show some default text (Not Available) if the property is non existent else the actual value. Find below the expression

if(empty(items('Apply_to_each')?['Country']),'Not Available',items('Apply_to_each')?['Country'])

You can use the above condition to show some default text for null values.

Summary:

The parse JSON action makes your flow cleaner with the availability of the properties from the dynamic content panel. It is always recommended to go through the generated schema of the action and do some clean up on unwanted or not going to be used properties and do updates to the different properties based on all scenarios of the expected content. Hope you have found this informational & thanks for reading. If you are visiting my blog for the first time, please do look at my other blogposts.

Do you like this article?

Subscribe to my blog with your email address using the widget on the right side or on the bottom of this page to have new articles sent directly to your inbox the moment I publish them.