error in Parse JSON Step in MS Flow

Question

Sorry for the long question, but I wanted to include all the JSON to get the best help.

we are getting an error parsing JSON from an HTTP Get request in our flow.

Here is the schema we are using in our Parse JSON step:

{
    "type": "object",
    "properties": {
        "d": {
            "type": "object",
            "properties": {
                "results": {
                    "type": "array",
                    "items": {
                        "type": "object",
                        "properties": {
                            "__metadata": {
                                "type": "object",
                                "properties": {
                                    "id": {
                                        "type": "string"
                                    },
                                    "uri": {
                                        "type": "string"
                                    },
                                    "type": {
                                        "type": "string"
                                    }
                                }
                            },
                            "Alerts": {
                                "type": "object",
                                "properties": {
                                    "__deferred": {
                                        "type": "object",
                                        "properties": {
                                            "uri": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            },
                            "Groups": {
                                "type": "object",
                                "properties": {
                                    "__deferred": {
                                        "type": "object",
                                        "properties": {
                                            "uri": {
                                                "type": "string"
                                            }
                                        }
                                    }
                                }
                            },
                            "Id": {
                                "type": "integer"
                            },
                            "IsHiddenInUI": {
                                "type": "boolean"
                            },
                            "LoginName": {
                                "type": "string"
                            },
                            "Title": {
                                "type": "string"
                            },
                            "PrincipalType": {
                                "type": "integer"
                            },
                            "Email": {
                                "type": "string"
                            },
                            "IsEmailAuthenticationGuestUser": {
                                "type": "boolean"
                            },
                            "IsShareByEmailGuestUser": {
                                "type": "boolean"
                            },
                            "IsSiteAdmin": {
                                "type": "boolean"
                            },
                            "UserId": {
                                "type": "object",
                                "properties": {
                                    "__metadata": {
                                        "type": "object",
                                        "properties": {
                                            "type": {
                                                "type": "string"
                                            }
                                        }
                                    },
                                    "NameId": {
                                        "type": "string"
                                    },
                                    "NameIdIssuer": {
                                        "type": "string"
                                    }
                                }
                            }
                        },
                        "required": [
                            "__metadata",
                            "Alerts",
                            "Groups",
                            "Id",
                            "IsHiddenInUI",
                            "LoginName",
                            "Title",
                            "PrincipalType",
                            "Email",
                            "IsEmailAuthenticationGuestUser",
                            "IsShareByEmailGuestUser",
                            "IsSiteAdmin",
                            "UserId"
                        ]
                    }
                }
            }
        }
    }
}

we are only interested in the Email property that we want to use to send approvals...

here are the INPUTS results of the Parse JSON step that fails:

{
  "d": {
    "results": [
      {
        "__metadata": {
          "id": "https://*********.sharepoint.com/_api/Web/GetUserById(1691)",
          "uri": "https://*********.sharepoint.com/_api/Web/GetUserById(1691)",
          "type": "SP.User"
        },
        "Alerts": {
          "__deferred": {
            "uri": "https://*********.sharepoint.com/_api/Web/GetUserById(1691)/Alerts"
          }
        },
        "Groups": {
          "__deferred": {
            "uri": "https://*********.sharepoint.com/_api/Web/GetUserById(1691)/Groups"
          }
        },
        "Id": 1691,
        "IsHiddenInUI": false,
        "LoginName": "i:0#.f|membership|[email protected]",
        "Title": "Firstname Surname",
        "PrincipalType": 1,
        "Email": "[email protected]",
        "Expiration": "",
        "IsEmailAuthenticationGuestUser": false,
        "IsShareByEmailGuestUser": false,
        "IsSiteAdmin": false,
        "UserId": {
          "__metadata": {
            "type": "SP.UserIdInfo"
          },
          "NameId": "10033fff9fe67a30",
          "NameIdIssuer": "urn:federation:microsoftonline"
        },
        "UserPrincipalName": "[email protected]"
      },
      }
    ]
  }

here is the schema from the run results of the Parse JSON that fails:

{
  "type": "object",
  "properties": {
    "d": {
      "type": "object",
      "properties": {
        "results": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": {
              "__metadata": {
                "type": "object",
                "properties": {
                  "id": {
                    "type": "string"
                  },
                  "uri": {
                    "type": "string"
                  },
                  "type": {
                    "type": "string"
                  }
                }
              },
              "Alerts": {
                "type": "object",
                "properties": {
                  "__deferred": {
                    "type": "object",
                    "properties": {
                      "uri": {
                        "type": "string"
                      }
                    }
                  }
                }
              },
              "Groups": {
                "type": "object",
                "properties": {
                  "__deferred": {
                    "type": "object",
                    "properties": {
                      "uri": {
                        "type": "string"
                      }
                    }
                  }
                }
              },
              "Id": {
                "type": "integer"
              },
              "IsHiddenInUI": {
                "type": "boolean"
              },
              "LoginName": {
                "type": "string"
              },
              "Title": {
                "type": "string"
              },
              "PrincipalType": {
                "type": "integer"
              },
              "Email": {
                "type": "string"
              },
              "IsEmailAuthenticationGuestUser": {
                "type": "boolean"
              },
              "IsShareByEmailGuestUser": {
                "type": "boolean"
              },
              "IsSiteAdmin": {
                "type": "boolean"
              },
              "UserId": {
                "type": "object",
                "properties": {
                  "__metadata": {
                    "type": "object",
                    "properties": {
                      "type": {
                        "type": "string"
                      }
                    }
                  },
                  "NameId": {
                    "type": "string"
                  },
                  "NameIdIssuer": {
                    "type": "string"
                  }
                }
              }
            },
            "required": [
              "__metadata",
              "Alerts",
              "Groups",
              "Id",
              "IsHiddenInUI",
              "LoginName",
              "Title",
              "PrincipalType",
              "Email",
              "IsEmailAuthenticationGuestUser",
              "IsShareByEmailGuestUser",
              "IsSiteAdmin",
              "UserId"
            ]
          }
        }
      }
    }
  }
}

and here is the erro in the step from the run history:

[
  {
    "message": "Invalid type. Expected Object but got Null.",
    "lineNumber": 0,
    "linePosition": 0,
    "path": "d.results[3].UserId",
    "schemaId": "#/properties/d/properties/results/items/properties/UserId",
    "errorType": "type",
    "childErrors": []
  }
]

so how can I change the schema to successfully retrieve the email

Answer 1

Try removing UserId from the 'required' array at the end of your JSON schema. Any values specified in that array must be present with every return/trigger, or the Flow will fail.

If 'Email' is the only value you truly need, I would make that the only required value.

Like this:

"required": [
              "Email"
            ]

Answer 2

The problem was caused by UserId being null in several elements of the data received from the HTTP Get, while that is set as required in the JSON Schema...

initially we solved it by using the below in the HTTP Get:

_api/web/sitegroups/getbyname('Legal Owners')/users?$filter=UserId+ne+null

Then we were given a better and more elegant solution on the Power Platform Community:

in the Apply to each step use:

body('Send_an_HTTP_request_to_SharePoint')?['d']?['results']

and within the loop use:

item()?['Email']