jsonsharepoint-onlinepower-automatesharepoint-api

Receiving empty brackets array of Sharepoint REST API request?

I'm working with Microsoft Power Automate to automate a workflow in Sharepoint. I already managed to get specified properties of all files in my list including the subfolders in one request (see simplified code below).

https://<myDomain>.sharepoint.com/sites/<mySite>/_api/web/lists/getbytitle('<myList>')/items?$select=Folder/Files/ListItemAllFields/ID,Folder/Files/ListItemAllFields/Title&$expand=Folder/Files/ListItemAllFields

I set the request header "accept: json;odata=nometadata" because there is no point for me receiving this metadata in my output.This request gives me the following JSON output body:

"value": [
    {
      "Folder": {
        "Files": [
          {
            "ListItemAllFields": {
              "Id": 1,  // Gives me back ID twice, Idk why but it
              "ID": 1   // works just fine and doesnt bother me
              "Title": "<fileTitle>"
            }
          },
... // Followed by all other 100+ files with ID and Title
]

Now the strange thing happens when I try to filter these files by ID or any other column as follows.

https://<myDomain>.sharepoint.com/sites/<mySite>/_api/web/lists/getbytitle('<myList>')/items?$select=Folder/Files/ListItemAllFields/ID,Folder/Files/ListItemAllFields/Title&$expand=Folder/Files/ListItemAllFields&$filter=(ID eq 1)or(ID eq 2)

When I add a filter statement to the HTTP request I get a JSON output body with empty brackets:

"value": [
    {}, // First file with ID = 1
    {}  // Second file with ID = 2
]

Its strange, because it gives me back the correct amount of JSON objects (I tried with a few combinations so Im sure about it), but doesn't expand the properties of the objects correctly. This leaves my output with empty brackets, where I would like to get my requested properties "ID" and "Title" as shown above.

As I understand it the syntax of the filter statement is correct, otherwise it wouldnt give me back the two requested objects. Do I have any syntax errors in my "expand"-statement? Does anyone know how to get or expand these objects correctly?

Solution

  • You are getting empty objects because the column you've mentioned in $select does not exists which is why its returning empty objects.

    Request without filter will return an array like this

    "value": [
{
  "Folder": {
    "Files": [
      {
        "ListItemAllFields": {
          "Id": 1,
          "Title": "<fileTitle>",
          "ID": 1
        }
      }
    ]
  }
},
{
  "Id": 1,
  "Title": "<fileTitle>",
  "ID": 1
}
}
]

    Since in your select statement you've mentioned 2 columns i.e

    Which does not exists for Files which is why its returning an empty object. It will work if you modify the url and add ID and Title in your select statement like below

    https://<myDomain>.sharepoint.com/sites/<mySite>/_api/web/lists/getbytitle('<myList>')/items?$select=ID,Title,Folder/Files/ListItemAllFields/ID,Folder/Files/ListItemAllFields/Title&$expand=Folder/Files/ListItemAllFields