The problem

I had to create a Power Automate Flow that took an array of Json objects and passed a single value from each object to a Stored procedure.

In the case I was working on, the JSON was CustomQuestionAnswers from a Microsoft Bookings trigger. Which looks something like…

[
  {
    "Answer": "Meeting Room 2",
    "AnswerOptions": [
      "Meeting Room 1",
      "Meeting Room 2",
      "Meeting Room 3",
      "Conference Room",
      "Gallery"
    ],
    "Question": "Delivery Venue",
    "QuestionID": "88888888-dddd-4444-9999-444444444444",
    "SelectedOptions": [
      1
    ]
  },
  {
    "Question": "Notes",
    "QuestionID": "77777777-bbbb-4444-bbbb-000000000000"
  },
  {
    "Answer": "5",
    "Question": "Total Number of people",
    "QuestionID": "33333333-bbbb-4444-bbbb-555555555555"
  }
]

The straightforward solution

The basic way to do this would be to set a variable in a Switch action inside an Apply to each action.

This would be ok if there were only a very small number of items in your array, as Switch actions are not the nicest things to work with (UI wise, that is). They are an elegant solution in theory (coming from a programming background), but from my experience, the best solution is the one that is transparent and easily maintainable and easy to debug (even by yourself when you have forgotten how it works).

Anyway, in this case my JSON array had 11 objects in it, this would have required 11 variables and 11 Case statements (plus a default) and that makes my flow so much larger than it needs to be.

This is what my prototype looked like…

A better solution

What I need to do is convert the array of JSON objects to a single JSON object of Key Value pairs, so that the values can be accessed via the key, like so…

The values can then be accessed like… variables('varJsonObject')?['Delivery Venue']

Here’s how to do it

For this example I created a manually triggered flow and added a variable for the JSON array, like so…

Next add a Select action to create an array of single Key Value pair objects

• If he input (From) is a string, wrap the variable in json, i.e. json(variables('varAnswersJsonArray'))
• The expression to enter for the Map items is item?['Question']

The output then looks like this…

Next is the hacky part, where we take the array of Key Value pair objects and use string manipulation to make it a single JSON object of Key Value pairs. To do this we use…

replace(replace(replace(string(body('Select_Key_Value_Pairs')), '[{', '{'),'}]', '}'), '},{', ',')

• Cast the Select action output to a string
• Remove the square brackets (no longer an array)
• Remove all but the beginning and ending curly braces (to make it a single object)
• This will not work if your value is itself a JSON object.

So it looks like this

I added another variable of Type Object to convert the above to an object, but that could all be done in one Initialize action if you want.

The end result looks like below. As you could imagine, that is a lot easier to debug than going through the Apply to each action waiting for it to hit the object you are wanting.

And finally we can get the value from the object by key, like so..

And the output is…

As a comparison to the original prototype (above), this is what I ended up with…

It’s a lot small and easier to see what is going on.