How to loop through a JSON with dynamic properties in Power Automate

Ok, this isn’t overly friendly but will get you a result.

Essentially, you need to convert your JSON to XML and do it that way.

These are the steps …

Compose Data

This is the JSON you provided simply pasted into that operation.

Compose XML

Here, we convert the JSON to XML. You do that via this expression …

xml(outputs('Compose_Data'))

Compose Timesheet Items

This is an XPath query to separate the items out into an array so you can loop over them.

xpath(xml(outputs('Compose_XML')), '//timesheets/*')

For Each Element

Is looping over the results of the previous operation.

outputs('Compose_Timesheet_Items')

Compose Item

Here, we decode the content of the item as it’s currently stored as base64.

xml(decodeBase64(item()['$content']))

Compose JSON Item

This is where we can compose a small JSON object that has the data you want from each item. It uses XPath to pull out each property.

{
  "id": @{first(xpath(xml(outputs('Compose_Item')), '//id/text()'))},
  "state": @{first(xpath(xml(outputs('Compose_Item')), '//state/text()'))},
  "user_id": @{first(xpath(xml(outputs('Compose_Item')), '//user_id/text()'))}
}

From there, you can do what you need to the object. You can add it to an array or process the data in line.

I should note as well, all of this can be done without looping.

If you use a Select operation instead of the loop, you can get it done a lot quicker. This is the definition for the Select, you’d need to pick out the parts to set it up …

{
  "type": "Select",
  "inputs": {
    "from": "@outputs('Compose_Timesheet_Items')",
    "select": {
      "id": "@first(xpath(xml(xml(decodeBase64(item()['$content']))), '//id/text()'))",
      "state": "@first(xpath(xml(xml(decodeBase64(item()['$content']))), '//state/text()'))",
      "user_id": "@first(xpath(xml(xml(decodeBase64(item()['$content']))), '//user_id/text()'))"
    }
  },
  "runAfter": {
    "Compose_Timesheet_Items": [
      "Succeeded"
    ]
  }
}