Does your data sometimes contain null values, and do you need to allow for this in Power Automate?

Handling null values in JSON schema is a common challenge while working with Power Automate. JSON schema is an essential component for validating payload structure and field data types in Power Automate flows.

However, when it comes to allowing null values, it could be a bit tricky, and errors might occur if not accounted for properly.

Power Automate works with JSON payloads, but it is essential to know how to create and modify JSON schema to accept null values. This way, you can seamlessly integrate with third-party APIs and ensure effective data processing.

By adapting your JSON schema, you avoid running into problems caused by null values which otherwise may result in flow errors or undesired behavior.

This post is going to show you exactly how to modify your JSON to allow null values in any field.

What is a Null Value

A null value represents the absence of a value or a non-existent object in a data structure.

This can be particularly important when processing data, such as in your JSON schema within Power Automate. Knowing how to handle null values is essential for accurate data processing and preventing unwanted errors.

When working with JSON schemas, a null value is represented by the keyword null. Understanding how null values work in a JSON schema is crucial for managing data correctly and ensuring seamless integration with your Power Automate workflows.

{
  "department": "Accounting",
  "name": "David Waterson",
  "jobTitle": null
}

For example, the above JSON contains no value for the jobTitle field.

Depending on how your Parse JSON schema is structured this might cause your flow to end in an unexpected error.

In scenarios where some data may be optional or missing, allowing null values in your JSON schema ensures that your workflow can still run smoothly, without causing errors due to the absence of expected fields. This can be especially helpful if you’re working with a variable number of fields or dynamically generated data.

{
  "type": ["string", "null"]
}

To permit null values in your JSON schema, define a field as a multi-type with both the desired data type and null. For instance, you can express a nullable string using an array containing both "string" and "null" types, such as above.

This schema definition allows for either a string value or a null value, enabling a more flexible representation of your data and accounting for any potential gaps.

Utilizing null values in your JSON schema is essential for avoiding disruptions in your Power Automate workflows due to flow errors.

Creating JSON Schema Using Generate from Sample

This section will show you how to create a JSON schema using the Generate from Sample feature.

To begin, you’ll access the Generate from Sample feature in Power Automate by adding a Parse JSON action to your flow. In the Parse JSON action, you can paste a JSON sample that includes the properties you want to allow for null values.

{
   "employees":[
      {
         "department":"Accounting",
         "name":"David Waterson",
         "jobTitle":"Senior Accountant"
      },
      {
         "department":"IT",
         "name":"Cory Harding",
         "jobTitle":"DevOps Engineer"
      }
   ]
}

Consider the above JSON sample. You will be able to use this to generate a JSON schema in the Parse JSON action.

1. Click on the Generate from sample button in the Parse JSON action.

2. Paste in the JSON sample in the Insert a sample JSON Payload popup.
3. Press the Done button.

{
    "type": "object",
    "properties": {
        "employees": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "department": {
                        "type": "string"
                    },
                    "name": {
                        "type": "string"
                    },
                    "jobTitle": {
                        "type": "string"
                    }
                },
                "required": [
                    "department",
                    "name",
                    "jobTitle"
                ]
            }
        }
    }
}

This will create a JSON schema based on your sample JSON. In this example, the schema will look like the above.

Notice that the jobTitle type is specified as string values. This will only allow the jobTitle field to accept a string value and any other data type will cause an error.

The Issue with Null Values

In Power Automate, dealing with null values in JSON schema can be challenging, especially when designing flows that handle various data types. Your JSON schema might need to allow null values for certain properties, allowing your applications to handle these cases flexibly.

Suppose you generate your JSON schema from an example. In that case, the JSON schema won’t allow for any variation in the permitted data types and the flow might later fail because it contains null values that weren’t anticipated.

One common issue you may face while working with JSON schema is the need to define a property that accepts multiple data types, including null values.

For example, you may have an optional string field in your JSON schema, and this field could be a string or null, depending on the input provided by the user.

To tackle this problem, you will need to modify your JSON schema definition that was generated from your sample to accept null values for specific properties.

This ensures that your Power Automate flow handles data correctly and minimizes potential errors that occur when processing and validating JSON objects.

By adjusting your JSON schema, you can increase the flexibility of your Power Automate flows and ensure that your applications can handle various data inputs effectively, even when dealing with null values.

Handling Null Values in JSON Schema with an Array

First, you will need to identify those properties that might have null values.

In this example, the jobTitle field might be empty and result in a null value in the JSON for the flow.

{
    "type": "object",
    "properties": {
        "employees": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "department": {
                        "type": "string"
                    },
                    "name": {
                        "type": "string"
                    },
                    "jobTitle": {
                        "type": ["string","null"]
                    }
                },
                "required": [
                    "department",
                    "name",
                    "jobTitle"
                ]
            }
        }
    }
}

To allow this property to accept null values as a valid type you will need to change the type within this property from "type": "string" to "type": ["string","null"].

This array will tell the flow that both string and null values are allowed for this field.

Handling Null Values in JSON Schema with anyOf Key

Another option for defining what data types are allowed is to use the anyOf keyword in your schema definition.

This keyword allows you to define multiple valid types for a single property. For instance, you can define a property to have both “string” and “null” types, making it possible for the property to accept either a string or a null value:

"jobTitle": {
  "anyOf": [
    { "type": "string" },
    { "type": "null" }
  ]
}

The above example shows how to use the anyOf key to specify either string or null type.

By using anyOf, Power Automate will not throw errors when it encounters a null value for the property. Instead, it will correctly parse and handle the JSON object, ensuring the flow functions as expected.

Handling Null Values in JSON Schema with an Empty Type

Suppose you want to allow the field to handle any data type, including null values.

"jobTitle": {}

This is also possible by omitting the type from the schema for this field such as in the above example.

⚠️ Warning: This will allow any data type for the field! This can be a problem if you want to allow string and null values, but still want to produce an error if the data is populated with a number.

Keep in mind that you should always aim to use clear and concise JSON schemas to convey information efficiently, and omitting the type can be confusing.

Handling Missing Fields in JSON Schema

One last possibility is that your JSON is missing the field entirely. This too will result in an error during the flow run.

{
   "employees":[
      {
         "department":"Accounting",
         "name":"David Waterson"
      },
      {
         "department":"IT",
         "name":"Cory Harding",
         "jobTitle":"DevOps Engineer"
      }
   ]
}

For example, suppose you have the above JSON. The first record is missing the jobTitle field. When this is used in the Parse JSON action, the action will fail.

"required": [
  "department",
  "name",
  "jobTitle"
]

This is because of the required keyword in the schema. The above section of the schema specifies jobTitle is a required field.

"required": [
  "department",
  "name"
]

This can be easily modified by removing the "jobTitle" field from the "required" array as seen above.

This will allow the flow to run without error if the Parse JSON action encounters a missing field.

Conclusions

Incorporating a more flexible JSON schema in your workflows can help reduce errors due to unexpected null values.

By modifying your JSON schema to include the "type": ["string", "null"] for properties that expect null values, you make your parsing process more robust.

These guidelines should help you enhance your current and future Power Automate workflows, ensuring that they are equipped to handle a variety of data sets.

Have you come across validation errors due to null values in your data? How did you handle these errors? Let me know in the comments!