Sort By Nested Array Value

[chaffee]

I have the following array and I would like to sort it by properties.startTime.    I know how to sort by a value at the top level but how by this startTime under the properties object.

[grantjenkins]

Are you able to provide a sample of your data? Ideally, the original data, and what you would expect it to look like once the sorting has happened.

[Chriddle]

sort(<array>, '<property>')

[grantjenkins]

@Chriddle They were asking about sorting based on nested array.

[Chriddle]

Hi @grantjenkins , might be also an array of objects.

Let's wait and see 😉

[chaffee]

I edited my original post with the data

[grantjenkins · Accepted solution]

@chaffee This should get what you're looking for. For filtering, we can easily access the sub-properties, so no issue there. For the sorting, I would suggest first promoting the sub-property to a top-level property, then can sort on that. See example below. Note that I've used some sample data, but the same format as what you have, so you should be able to use the expressions without having to modify.

See full flow below. I'll go into each of the actions.

Data is a Compose that contains the object. Note that I created a sample based on what you had for testing purposes. The data I used is below. Three of the statuses are Succeeded and one Running. We should only see the three Succeeded ones, sorted by properties/startTime.

{
    "statusCode": 200,
    "headers": {
        "Content-Length": "45276"
    },
    "body": {
        "value": [
            {
                "name": "Alpha",
                "id": "xxxx",
                "type": "run",
                "properties": {
                    "startTime": "2023-02-01T14:01:01.1642776Z",
                    "status": "Succeeded"
                }
            },
            {
                "name": "Beta",
                "id": "yyyy",
                "type": "run",
                "properties": {
                    "startTime": "2023-01-01T14:01:01.1642776Z",
                    "status": "Succeeded"
                }
            },
            {
                "name": "Gamma",
                "id": "zzzz",
                "type": "run",
                "properties": {
                    "startTime": "2023-02-01T12:01:01.1642776Z",
                    "status": "Running"
                }
            },
            {
                "name": "Delta",
                "id": "zzzz",
                "type": "run",
                "properties": {
                    "startTime": "2023-02-01T12:01:01.1642776Z",
                    "status": "Succeeded"
                }
            }
        ]
    }
}

The first thing I would do is filter out the data so only items with status of Succeeded are returned. I've used the following expressions in my Filter array. This will output just three objects, but still not sorted. Note that the input here would come from wherever your data is stored in your flow.

//Get the array within the main object from body/value
outputs('Data')?['body/value']

//Filter on properties/status
item()?['properties/status']

Select will promote our properties/startTime property to the top-level of each object so we can sort on it. The expression used is below.

//Add a new property called startTime at the top-level that contains the value from properties/startTime, and combines this with the existing object.

addProperty(item(), 'startTime', item()?['properties/startTime'])

If we ran the flow now, we would see the following output (still not sorted). Note the startTime property at the top-level of each object.

[
  {
    "name": "Alpha",
    "id": "xxxx",
    "type": "run",
    "properties": {
      "startTime": "2023-02-01T14:01:01.1642776Z",
      "status": "Succeeded"
    },
    "startTime": "2023-02-01T14:01:01.1642776Z"
  },
  {
    "name": "Beta",
    "id": "yyyy",
    "type": "run",
    "properties": {
      "startTime": "2023-01-01T14:01:01.1642776Z",
      "status": "Succeeded"
    },
    "startTime": "2023-01-01T14:01:01.1642776Z"
  },
  {
    "name": "Delta",
    "id": "zzzz",
    "type": "run",
    "properties": {
      "startTime": "2023-02-01T12:01:01.1642776Z",
      "status": "Succeeded"
    },
    "startTime": "2023-02-01T12:01:01.1642776Z"
  }
]

Sorted is a Compose uses the following expression to sort the data. We can only do this since the startTime property is a top-level property.

//Sort the objects by startTime in ascending order
sort(body('Select'), 'startTime')

//Sort the objects by startTime in descending order
reverse(sort(body('Select'), 'startTime'))

If we sorted in ascending order, we would get the following output.

[
  {
    "name": "Beta",
    "id": "yyyy",
    "type": "run",
    "properties": {
      "startTime": "2023-01-01T14:01:01.1642776Z",
      "status": "Succeeded"
    },
    "startTime": "2023-01-01T14:01:01.1642776Z"
  },
  {
    "name": "Delta",
    "id": "zzzz",
    "type": "run",
    "properties": {
      "startTime": "2023-02-01T12:01:01.1642776Z",
      "status": "Succeeded"
    },
    "startTime": "2023-02-01T12:01:01.1642776Z"
  },
  {
    "name": "Alpha",
    "id": "xxxx",
    "type": "run",
    "properties": {
      "startTime": "2023-02-01T14:01:01.1642776Z",
      "status": "Succeeded"
    },
    "startTime": "2023-02-01T14:01:01.1642776Z"
  }
]

Additional (only if required): I would expect that leaving the startTime property we promoted to the top-level would be fine. However, if you did need to remove it, then this is how you could remove it after you've sorted your data.

Add another Select (I've called mine Select Sorted) that uses the output from Sorted, and has the following expression to remove the property we added.

removeProperty(item(), 'startTime')

This would give us the original data (properties), filtered and sorted.

[
  {
    "name": "Beta",
    "id": "yyyy",
    "type": "run",
    "properties": {
      "startTime": "2023-01-01T14:01:01.1642776Z",
      "status": "Succeeded"
    }
  },
  {
    "name": "Delta",
    "id": "zzzz",
    "type": "run",
    "properties": {
      "startTime": "2023-02-01T12:01:01.1642776Z",
      "status": "Succeeded"
    }
  },
  {
    "name": "Alpha",
    "id": "xxxx",
    "type": "run",
    "properties": {
      "startTime": "2023-02-01T14:01:01.1642776Z",
      "status": "Succeeded"
    }
  }
]

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[chaffee]

@grantjenkins 

Thanks much for the incredibly detailed answer!