Power Automate: What is a JSON Schema?

Although Power Automate is a low code application, there are some concepts that we should be aware of when building our Flows. This can be tricky for some people who want to use Power Automate but don’t have any programming background. Today we’ll talk about JSON and the concept of a schema.

I’ve talked about it briefly in the “Power Automate: Power Automate as a Web Service” about JSON Schemas but let’s look at them today and try to make them accessible to everyone.

This article is not intended to be the ultimate reference for JSON Schemas. There’s already one. In the scope of Power Automate, I want to show you where do schemas show up and how to read them in case you need to adjust something. You would rarely have to build one by hand.

If you get the “Invalid type. Expected String but got Null” error, you can jump to the section where I explain how to fix it.

If you get the ”Invalid type. Expected String but got Null” error, you can jump to the section where I explain how to fix it.

Disclaimer out of the way, let’s look at some basic concepts.

Some basic concepts

The first thing you need to know is that Power Automate communicates with the various systems using APIs. These are like a restaurant menu. You pick the food you want, and it’s provided to you. But to work, they need to talk the same “language,” and that’s where JSON comes. JSON is a standard that defines a structured way for data to be transferred.

If you do a SharePoint “Get Item.”

{
    "statusCode": 200,
    "headers": {
        ...
    },
    "body": {
        "value": [
            {
                "@odata.etag": "\"2\"",
                "ItemInternalId": "1",
                "ID": 1,
                "Title": "Sabrina Myers",
                "First_x0020_Name": "Sabrina",
                "Last_x0020_Name": "Myers",
                "Age": 28.0,
                "Phone": "691-6045-67",
                "Experience_x0020__x0028_Years_x0": 5.0,
                "Salary": 3046.0,
                "Employee": true,
                "Modified": "2021-03-25T18:26:20Z",
                "Created": "2021-03-25T08:56:58Z",
                "Author": {
                    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                    "Claims": "i:0#.f|membership|manuel@manueltgomes.com",
                    "DisplayName": "Manuel Gomes",
                    "Email": "manuel@manueltgomes.com",
                    "Picture": "https://manueltgomescom.sharepoint.com/sites/Test/_layouts/15/UserPhoto.aspx?Size=L&AccountName=manuel@manueltgomes.com",
                    "Department": null,
                    "JobTitle": null
                },
                "Author#Claims": "i:0#.f|membership|manuel@manueltgomes.com",
                "Editor": {
                    "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                    "Claims": "i:0#.f|membership|manuel@manueltgomes.com",
                    "DisplayName": "Manuel Gomes",
                    "Email": "manuel@manueltgomes.com",
                    "Picture": "https://manueltgomescom.sharepoint.com/sites/Test/_layouts/15/UserPhoto.aspx?Size=L&AccountName=manuel@manueltgomes.com",
                    "Department": null,
                    "JobTitle": null
                },
                "Editor#Claims": "i:0#.f|membership|manuel@manueltgomes.com",
                "{Identifier}": "Lists%252fTest%2bUsers%252f1_.000",
                "{IsFolder}": false,
                "{Thumbnail}": {
                    "Large": null,
                    "Medium": null,
                    "Small": null
                },
                "{Link}": "https://manueltgomescom.sharepoint.com/sites/Test/_layouts/15/listform.aspx?PageType=4&ListId=72a2429b%2Db880%2D4031%2Dbd69%2D911da6d98cb3&ID=1&ContentTypeID=0x01002DE8C719D34B99458A8654E0C65F7E08",
                "{Name}": "Sabrina Myers",
                "{FilenameWithExtension}": "Sabrina Myers",
                "{Path}": "Lists/Test Users/",
                "{FullPath}": "Lists/Test Users/1_.000",
                "{HasAttachments}": false,
                "{VersionNumber}": "2.0"
            }
        ]
    }
}

It looks scary, but once you understand the syntax, it makes sense. I’ll go over the syntax in another article, but I just wanted to show you something familiar to understand the next concept.

JSON Schema

As mentioned before, JSON has a structure that can transport data between services. But it’s important that the data that arrives at the destination is something that you expect and can use. For example, if you call the restaurant ordering a pizza, and you get sushi, you look at it and say, “that’s not what I ordered.” The JSON Schema allows you to do exactly that. To define what you’ve ordered and, if you get something that is not expected, the Flow will warn you so that you don’t use invalid data.

Building a JSON Schema

Most of the time, we either get the schema for free, or we use the handy “Generate from Sample,” where the schema is built for us.

To do that, copy the JSON you want to generate the schema:

And you’ll get the schema generated for you.

I won’t tell you how to build one from scratch since it’s seldom necessary, but I wanted to show you this feature to create one if you don’t know-how.

Overview

The JSON Schema will define the structure of the JSON that you expect. For example, if you’re getting a list of items from a service you expect:

An array of elements
In each element, a key: value pair.
The value has a specific type.

Power Automate will then use this information and will enable you to use the elements after like the array:

You can do an “Apply to Each” and get all elements of that array. It’s a much nicer way to do it than having to parse a text file.

When you do a “Get Items” for example, all of this is done in the background automatically, but you need to know what’s happening in case you need to access an API for example and get the raw JSON from it.

The structure

JSON Schemas can be quite complex, but here’s what you need to know.

This is the most over-simplification you’ll get anywhere. The objective again is not for you to build by hand your JSON Schema. The idea is for you to know how to read one and know what’s happening. It’s also not detailed for the same reason, so if you want to understand all the “nooks and crannies,” you can go to the reference page and learn more.

The type of value returned is quite well defined. If it’s a string:

{ "type": "string" }

You have other types, but here are the main ones:

Object – a “box” where you can put other types “inside.” You can think of it as a property where the value is another property. In the example above, the “body” is one:

"body": {
            "type": "object",
            "properties": {
                "value": {
...
			}
		}
	}

Array – a defined list of elements with properties.

"type": "array",
	"items": {
		...
	}

A property pair. An object is a property, but I wanted to separate it because it’s a property with other properties inside. You can think of a property as a key (the way you’ll find what you want) and the value. For example:

"ID": {
	"type": "integer"
},
"Title": {
	"type": "string"
}

Again there’s more, but this is what you need to know.

Go to top

The “null” error

I want to focus a little on the type because there are times where you need to edit it. When you define a type, you’re saying that you always want to get a value from that type. But there are times where there’s no value, so null is returned. The value null is not a string or an integer. It’s “nothing,” so you may get the error:

Invalid type. Expected String but got Null

Pro Tip:
Power Automate will, most of the times, define the schema as with only one type, like you see in the examples above. But, if there’s the possibility of a “null” value, you need to edit the schema to ensure that your Flow won’t break. In my experience, this is the only time that you will need to edit yourself the schema.

To solve this, you need to make a small change. You need to indicate in the schema that there are multiple possible values to be returned. For example, a “string” or a “null.” Since we’re talking about multiple values, we’ll use an array, and we know how an array is defined (with “[” and “]”).

Here’s an example:

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

In this case, you’re defining that the “Title” is optional so that you may get a value or not.

It’s very important, for these cases, to validate the data. If you get a null instead of a string, for example, your Flow should know how to deal with it, like having a default value or notifying you that this happened. Always be in control of what’s stored so as not to store invalid data.

The “Invalid Type” is a common error, but with a simple solution.

Go to top

Final thoughts

JSON Schemas are super powerful and will help Power Automate to know what data to expect. It’s also a good way for you to define that if the data is not in the format you want, the data is invalid, and you won’t even parse it.

Again, you won’t need to define it by hand, but as you see above, some errors need some tweaks in the schema.

Have a suggestion of your own or disagree with something I said? Leave a comment or interact on Twitter and be sure to check out other Power Automate-related articles here.

Photo by Spencer on Unsplash