A friend was struggling to get the desirable output of a “Get from SharePoint” action, there is a way or another to do it -depends on what you do with the data in upcoming actions. So, I got this not-so-orthodox method to create an JSON object that fits perfectly in this friend’s need. So,lets elaborate…

Return of an action

Most Power Automate actions return JSON as their output. This is from a Get Items (from a SharePoint list)

Now, let’s suppose that you will need to populate a word document with that return —so, wouldn’t it be simpler if we had this as a table? Like that:

Notice that this table doesn’t have header or indexes, but that on purpose (tip for algorithm and data structure nerds: hash table)

Possibilities

So, let’s explore some possibilities using the figure 1 output. Firstly, using the “Select” data action, let’s try to map that output:

This will be the output:

{
    "body": [
        {
            "Should employee use Helmet?": "Yes"
        },
        {
            "Lunch at mid-day?": "Yes"
        },
        {
            "Coffe is free?": "No"
        }
    ]
}

Well, it’s quite better; maybe you can filter for the desirable answer…

It’s better, but this isn’t the answer:

And there is more, still an Array, to access it the index will be needed:

Here you’re already noticing where this is leading… a lot of repetition and hard work
We can list a bunch of other possibilities here, like use an apply to each loop, but again, it’s counter-produtive.

JSON object instead of an Array of objects

Let’s back to the figure 2 (desirable table). Here is the deal, acess the answer directly from a table.

For now, we’ve been using the return after the body/value of the Get Items SharePoint action and the Select action, that’s an Array of objects.
What we’ll need is to manipulate that return to build the table, at glance you can try to make this by hand:

The output is really what we need, but this is not right, if we have 10 or more questions to map?—really don’t make sense all that work.

Back to Get Items output, let’s build an object!

Alright, for now you’ve seen dificult or time-consuming ways to accessing the answers of the Get Item action, let’s manipulate that output to build the object shown in the figure 8 and them access the desirable answer

Focusing in the output of the action, more specifically at the “value” array:

Summaring what we’ll do: map that object with a Select Action to get rid of the keys “Title” and “Answer” to compose an output identically to “Try to map” in figure 3 and manipulate the output to avoid using Arrays—sound weird, but you’ll see.

After all, we need a JSON, isn’t? The Parse JSON action sometimes can be handly, but trust me, it’s easy to make a mistake or get a different output from your action that will break your action; and probably you will fix it, them will break again, them you fix it…

For that, with the lack of possibilities in Power Automate, you can manipulate the array as a string removing all the structures that form the array of objects ( like the [{ at beggining ) and them converting to a JSON using the JSON function in Power Automate.

1. Map it

With that output now we’ll manipulate it as a string

2. Manipulate array as string

This is way too far to be beautifull, but solves a lot of effort. Maybe you want to understand for the first time what’s is going on, but whem you get it you can save the expression and just apply when is needed.

Those replaces functions replaces the structures to form the object as we want, like { “Should employee use Helmet?” : “Yes” } and them, turn it to a JSON with json function

The code:

json(
	replace(
		replace(
			replace(
				string(body('Map_Question_Answer')),
				'},{',
				' ,'
			),
			'[{',
			'{'
		),
		'}]',
		'}'
	)
)

With that in hands the expression will deal with bigger lists if necessary, without the need of further steps, except the action where you going to access that data:

Conclusion

Depending on your goals some more complex methods are needed, but complexity isn’t equal to bigger implementations, this is well exposed in that discussed case

In my implementations, especially in Power Automate, thinking in the data structure can spare a lot of energy and time, also leveraging the quality of your projects.

When dealing with larger data, Power Automate sometimes has its limits (some lack of functions, capacity limits, etc), in our case we manipulate the string instead of using JSON parser, it’s way too easier than the parser and it’s not susceptible to erros.