Extracting information from a HTTP request

[Tjeremiah]

Hi all,

I am trying to collect information on what is in my site recycle bin periodically in case I need to restore it.

Using part of the process outlined in THIS post, I am sending a HTTP request, and getting a response back.

I want to be able to pass the below into an array I can then work with further.

                                    "__metadata",
                                    "Author",
                                    "DeletedBy",
                                    "AuthorEmail",
                                    "AuthorName",
                                    "DeletedByEmail",
                                    "DeletedByName",
                                    "DeletedDate",
                                    "DeletedDateLocalFormatted",
                                    "DirName",
                                    "DirNamePath",
                                    "Id",
                                    "ItemState",
                                    "ItemType",
                                    "LeafName",
                                    "LeafNamePath",
                                    "Size",
                                    "Title"

Whenever i try to utilize the outputs of the HTTP request, i get an error:

The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['body']?['d']?['results']' is of type 'Null'. The result must be a valid array.

I can see that ['results'] is not empty/null:

,"body":{"d":{"results":[{"__metadata":{"id":"https://mytennent.sharepoint.com/sites/mysite/_api/Web/RecycleBin(etc....

The full Parse JSON schema is below. This was created using the 'Generate from sample' function of the flow card.

{
    "type": "object",
    "properties": {
        "statusCode": {
            "type": "integer"
        },
        "headers": {
            "type": "object",
            "properties": {
                "Transfer-Encoding": {
                    "type": "string"
                },
                "Vary": {
                    "type": "string"
                },
                "X-SharePointHealthScore": {
                    "type": "string"
                },
                "X-SP-SERVERSTATE": {
                    "type": "string"
                },
                "DATASERVICEVERSION": {
                    "type": "string"
                },
                "SPClientServiceRequestDuration": {
                    "type": "string"
                },
                "SPRequestGuid": {
                    "type": "string"
                },
                "request-id": {
                    "type": "string"
                },
                "MS-CV": {
                    "type": "string"
                },
                "Strict-Transport-Security": {
                    "type": "string"
                },
                "X-FRAME-OPTIONS": {
                    "type": "string"
                },
                "Content-Security-Policy": {
                    "type": "string"
                },
                "MicrosoftSharePointTeamServices": {
                    "type": "string"
                },
                "X-Content-Type-Options": {
                    "type": "string"
                },
                "X-MS-InvokeApp": {
                    "type": "string"
                },
                "x-ms-request-id": {
                    "type": "string"
                },
                "Cache-Control": {
                    "type": "string"
                },
                "P3P": {
                    "type": "string"
                },
                "Set-Cookie": {
                    "type": "string"
                },
                "X-AspNet-Version": {
                    "type": "string"
                },
                "X-Powered-By": {
                    "type": "string"
                },
                "Timing-Allow-Origin": {
                    "type": "string"
                },
                "x-ms-apihub-cached-response": {
                    "type": "string"
                },
                "Date": {
                    "type": "string"
                },
                "Content-Type": {
                    "type": "string"
                },
                "Expires": {
                    "type": "string"
                },
                "Last-Modified": {
                    "type": "string"
                },
                "Content-Length": {
                    "type": "string"
                }
            }
        },
        "body": {
            "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"
                                            }
                                        }
                                    },
                                    "Author": {
                                        "type": "object",
                                        "properties": {
                                            "__deferred": {
                                                "type": "object",
                                                "properties": {
                                                    "uri": {
                                                        "type": "string"
                                                    }
                                                }
                                            }
                                        }
                                    },
                                    "DeletedBy": {
                                        "type": "object",
                                        "properties": {
                                            "__deferred": {
                                                "type": "object",
                                                "properties": {
                                                    "uri": {
                                                        "type": "string"
                                                    }
                                                }
                                            }
                                        }
                                    },
                                    "AuthorEmail": {
                                        "type": "string"
                                    },
                                    "AuthorName": {
                                        "type": "string"
                                    },
                                    "DeletedByEmail": {
                                        "type": "string"
                                    },
                                    "DeletedByName": {
                                        "type": "string"
                                    },
                                    "DeletedDate": {
                                        "type": "string"
                                    },
                                    "DeletedDateLocalFormatted": {
                                        "type": "string"
                                    },
                                    "DirName": {
                                        "type": "string"
                                    },
                                    "DirNamePath": {
                                        "type": "object",
                                        "properties": {
                                            "__metadata": {
                                                "type": "object",
                                                "properties": {
                                                    "type": {
                                                        "type": "string"
                                                    }
                                                }
                                            },
                                            "DecodedUrl": {
                                                "type": "string"
                                            }
                                        }
                                    },
                                    "Id": {
                                        "type": "string"
                                    },
                                    "ItemState": {
                                        "type": "integer"
                                    },
                                    "ItemType": {
                                        "type": "integer"
                                    },
                                    "LeafName": {
                                        "type": "string"
                                    },
                                    "LeafNamePath": {
                                        "type": "object",
                                        "properties": {
                                            "__metadata": {
                                                "type": "object",
                                                "properties": {
                                                    "type": {
                                                        "type": "string"
                                                    }
                                                }
                                            },
                                            "DecodedUrl": {
                                                "type": "string"
                                            }
                                        }
                                    },
                                    "Size": {
                                        "type": "string"
                                    },
                                    "Title": {
                                        "type": "string"
                                    }
                                },
                                "required": [
                                    "__metadata",
                                    "Author",
                                    "DeletedBy",
                                    "AuthorEmail",
                                    "AuthorName",
                                    "DeletedByEmail",
                                    "DeletedByName",
                                    "DeletedDate",
                                    "DeletedDateLocalFormatted",
                                    "DirName",
                                    "DirNamePath",
                                    "Id",
                                    "ItemState",
                                    "ItemType",
                                    "LeafName",
                                    "LeafNamePath",
                                    "Size",
                                    "Title"
                                ]
                            }
                        }
                    }
                }
            }
        }
    }
}

Hopefully someone can spot my mistake.

Thanks in advance!

[trad · Accepted solution]

I think that when you're using body('Parse JSON') it's already looking into the "body" property of your JSON payload. So when you use body('Parse_JSON')?['body']?['d']?['results'] it is looking for a second "body" property within the first "body" property. Try using body('Parse_JSON')?['d']?['results'] similar to the article you linked which is using body('Get_files_from_Recycle_Bin')['d']['results']

[Tjeremiah]

Thanks @trad,

You were right, that was part of the issue.

I also had to modify the Parse JSON quite a bit. I ended up with a second parse JSON on the output of the first to drill down into the object I actually want.

Happy to hear from someone with more knowledge, but this is how I ended up getting what I wanted out,

Send a HTTP request

Filter array (converts the object returned to an array)

Apply to each

  Parse JSON

{
    "type": "object",
    "properties": {
        "content": {
            "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"
                                            }
                                        }
                                    },
                                    "Author": {
                                        "type": "object",
                                        "properties": {
                                            "__deferred": {
                                                "type": "object",
                                                "properties": {
                                                    "uri": {
                                                        "type": "string"
                                                    }
                                                }
                                            }
                                        }
                                    },
                                    "DeletedBy": {
                                        "type": "object",
                                        "properties": {
                                            "__deferred": {
                                                "type": "object",
                                                "properties": {
                                                    "uri": {
                                                        "type": "string"
                                                    }
                                                }
                                            }
                                        }
                                    },
                                    "AuthorEmail": {
                                        "type": "string"
                                    },
                                    "AuthorName": {
                                        "type": "string"
                                    },
                                    "DeletedByEmail": {
                                        "type": "string"
                                    },
                                    "DeletedByName": {
                                        "type": "string"
                                    },
                                    "DeletedDate": {
                                        "type": "string"
                                    },
                                    "DeletedDateLocalFormatted": {
                                        "type": "string"
                                    },
                                    "DirName": {
                                        "type": "string"
                                    },
                                    "DirNamePath": {
                                        "type": "object",
                                        "properties": {
                                            "__metadata": {
                                                "type": "object",
                                                "properties": {
                                                    "type": {
                                                        "type": "string"
                                                    }
                                                }
                                            },
                                            "DecodedUrl": {
                                                "type": "string"
                                            }
                                        }
                                    },
                                    "Id": {
                                        "type": "string"
                                    },
                                    "ItemState": {
                                        "type": "integer"
                                    },
                                    "ItemType": {
                                        "type": "integer"
                                    },
                                    "LeafName": {
                                        "type": "string"
                                    },
                                    "LeafNamePath": {
                                        "type": "object",
                                        "properties": {
                                            "__metadata": {
                                                "type": "object",
                                                "properties": {
                                                    "type": {
                                                        "type": "string"
                                                    }
                                                }
                                            },
                                            "DecodedUrl": {
                                                "type": "string"
                                            }
                                        }
                                    },
                                    "Size": {
                                        "type": "string"
                                    },
                                    "Title": {
                                        "type": "string"
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

  Parse JSON 2

{
    "type": "object",
    "properties": {
        "body": {
            "type": "object",
            "properties": {
                "AuthorEmail": {
                    "type": "string"
                },
                "AuthorName": {
                    "type": "string"
                },
                "DeletedByEmail": {
                    "type": "string"
                },
                "DeletedByName": {
                    "type": "string"
                },
                "DeletedDate": {
                    "type": "string"
                },
                "DeletedDateLocalFormatted": {
                    "type": "string"
                },
                "DirName": {
                    "type": "string"
                },
                "DirNamePath": {
                    "type": "object",
                    "properties": {
                        "__metadata": {
                            "type": "object",
                            "properties": {
                                "type": {
                                    "type": "string"
                                }
                            }
                        },
                        "DecodedUrl": {
                            "type": "string"
                        }
                    }
                },
                "Id": {
                    "type": "string"
                },
                "ItemState": {
                    "type": "integer"
                },
                "ItemType": {
                    "type": "integer"
                },
                "LeafName": {
                    "type": "string"
                },
                "LeafNamePath": {
                    "type": "object",
                    "properties": {
                        "__metadata": {
                            "type": "object",
                            "properties": {
                                "type": {
                                    "type": "string"
                                }
                            }
                        },
                        "DecodedUrl": {
                            "type": "string"
                        }
                    }
                },
                "Size": {
                    "type": "string"
                },
                "Title": {
                    "type": "string"
                }
            }
        }
    }
}

  Append to array variable