Power Automate: Invalid type. Expected Object but got Null.
As you may know, a JSON body is similar to a table but is raw data without any properties. The data just follow the JSON standard. You can format it into objects for further querying. That is why we use the Parse JSON task in Power Automate. You may face an error if the data is not properly declared.
A Schema tells the table what data can be filled in. The following is the schema of the JSON example above.
Imagine that you received JSON raw data and want to fill it into a table. The Schema in the Parse JSON task describes each column's attributes of the table. Then the matched JSON values will be filled into the corresponding columns.
For instance, the following code is a piece of a JSON body.
〰〰〰
"server-pool": {
"data": {
"id": "pool-123",
"kind": "agent-pools"
}
}
The Parse JSON task formats the raw data into a table like a screenshot below. Each line from the bottom is the "value" of the above line. It is a nested table.
A Schema tells the table what data can be filled in. The following is the schema of the JSON example above.
〰〰〰
"agent-pool": {
"type": "object",
"properties": {
"data": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"kind": {
"type": "string"
}
}
}
}
}
As you can see, each "table column" has a "type" and maybe a "property".
You may see the following error when running the Parse JSON task in a Power Automate workflow.
Flow run failed.
"message": "Invalid type. Expected Object but got Null."
"message": "Invalid type. Expected Object but got Null."
The reason is that some "columns" received empty values. An example of JSON raw data is like this:
〰〰〰
"server-pool": {
"data": {
"id": "pool-123",
"kind": null
}
}
The Parse JSON task cannot fill the kind "column" because the schema above says the "column" must be a string. So, Power Automate throws the error above.
To avoid this problem, you must declare multiple types for the "column". The schema below tells the kind "column" could be a string or null.
〰〰〰
"agent-pool": {
"type": "object",
"properties": {
"data": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"kind": {
"type": [ "string", "null" ] # Modify 1
}
}
}
}
}
It is simple. However, as I mentioned earlier, JSON is a "nested table". What if the data "column" is no values?
For instance, we received the following JSON raw data:
〰〰〰
"agent-pool": {
"data": null
}
It is a similar solution. The type of the data "column" is an object in the schema above. We need to declare it as multiple types.
〰〰〰
"agent-pool": {
"type": "object",
"properties": {
"data": {
"type": ["object", "null"], # Modify 2
"properties": {
"id": {
"type": "string"
},
"kind": {
"type": [ "string", "null" ] # Modify 1
}
}
}
}
}