CODEWITHSUNDEEP
power-automate
Huan Pham
Huan Pham

Reputation: 3

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

I want to use Power Automate to call TSheet timesheets api to get the data and process it. But I'm having difficulties looping through the responded data from TSheet. So here is an example of the responded json:

{
    "results": {
        "timesheets": {
            "1234567": {
                "id": 1234567,
                "state": "CLOSED",
                "user_id": 123456
            },
            "2345678": {
                "id": 415628812,
                "state": "APPROVED",
                "user_id": 234567
            }
        }
    }
}

I have used "Parse JSON" and "Compose" actions trying to format this JSON to an array so that I can use it on the "Apply to Each" action but I haven't figured out how. Passing this json object to "Apply to Each" action will fail because it requires array.

Any help will be appreciated.

Upvotes: 0

Views: 1198

Answers (1)

Skin
Skin

Reputation: 11262

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

Flow

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"
    ]
  }
}

Select

Upvotes: 0

Related Questions