How to use Parse JSON on SQL Stored Procedure results in Microsoft Power Automate

By - June 16, 2020

Microsoft Power Automate can parse the results of a SQL Stored Procedure from an On-Premise SQL Gateway connection, but it is not very intuitive. You need to take the ResultSets from the Stored Procedure and remove some of the formatting at the beginning and end to enable the Parse JSON step to correctly process it. Otherwise, you will get errors like the below on the Parse JSON step or the following Apply to Each step:

"invalid type. Expected Array but got Object"

"The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression ... is of type 'Object'. The result must be a valid array"

The problem is that the ResultSets value returned by the Stored Procedure step is in a slightly different format than the Parse JSON step is expecting. The SQL Stored Procedure step in Power Automate always outputs the first result set formatted as an Object containing the array “Table1” instead of just outputting the array itself. To use a basic array of names as an example, this is what the Execute Stored Procedure step will return:

{

"Table1": [

{

"FirstName": "Sam",

"LastName": "Stern"

}

]

}

But the Parse JSON step only wants the array itself, like this:

[

{

"FirstName": "Sam",

"LastName": "Stern"

}

]

So we have to update the format for the Schema input and the Content input in the Parse JSON step.

The easiest way to set the Schema is to create your “Execute Stored Procedure” step and add a “Compose” step that inputs the “ResultSets” from the Stored Procedure.

Then run your Flow. The Compose output should look like the first example above, with the array wrapped in “Table1”. Copy the Output from the Compose step into a Notepad file. Manually delete the first “{” and “‘Table1’ :” from the beginning and the corresponding “}” from the end, which should leave it looking like the 2nd example shown above. This can now generate a working Schema, so add your Parse JSON step and hit “Generate from sample” and paste it in, then hit “Done”:

Now that the schema is set we need to format the “Content” input correctly, which is a little harder. The way I found to do this is to convert the whole ResultSets into a string then take the Substring excluding both the first 10 characters (removing { “Table1”:) and the last 1 character (removing }). I did this in 4 Compose Steps stored inside a Scope, but you could alternatively combine it into a single larger step if you prefer.

The 1st step transforms the “ResultSets” into a string:

string(body('Execute_stored_procedure_(V2)')?['resultsets'])
The 2nd step strips the first 10 characters off by taking a substring starting at the index 10:
substring(outputs('Compose_Result_Set_as_String'),10)
The 3rd step gets the length of the remaining string minus 1 to use in the next substring step, which will remove the last character:
sub(length(outputs('Compose_Result_Set_stripped')),1)
The 4th and final step takes the substring excluding the last character, which leaves only the characters we need.
substring(outputs('Compose_Result_Set_stripped'),0,outputs('Compose_Length_of_Substring_minus_1'))
Note that the output is still in string format, so populate your Parse JSON step Content field with the JSON() function to convert it back:
json(outputs('Compose_Result_Set_ready_to_be_an_array'))
With those adjustments, you should now be able to successfully parse JSON on the output of your Execute Stored Procedure step!
Note another common error can occur around the “type” of each field specified in the schema. When the schema sets an explicit type for a field, but the actual data returns Null for a record then it can cause an error. To mitigate that, just remove the “type” line from each field and remove it from the list of required attributes. The default schema generated may look like this:
{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "FirstName": {
                "type": "string"
            },
            "LastName": {
                "type": "string"
            }
        },
        "required": [
            "FirstName",
            "LastName"
        ]
    }
}
To resolve those errors, clear out the “type” lines for each field, then remove each field from the “required” area. Your result should look like this:
{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "FirstName": {
            },
            "LastName": {
            }
        },
        "required": [
        ]
    }
}
This should resolve that issue. You can leave the fields that are actually required if preferred.

Sam is a Senior Consultant in RSM's Dynamics practice. He has experience working with many aspects of Dynamics CE as well as integration technologies like Scribe and SmartConnect.

4 Comments

Marian June 23, 2020 at 9:43 am

Hi,
this looked like a great solution of the JSON issue I bumped into.
But, I think in the 4 steps you have some wrong formulas?
In Step 2 you use a Substring. BUt this needs 2 parameters. That is where I am stuck now.
In Step 3 and 4 you have the same expressions…
Can you help me out?
How to get Step 2 fixed in the first place and than Step 3 and 4.

Thanks!

Reply
    Sam Stern June 23, 2020 at 2:20 pm

    Hello Marian!

    Glad this is useful for you! I doubled checked Step 2 and it does look like both parameters are present:

    substring(outputs(‘Compose_Result_Set_as_String’),10)

    The first parameter is the output from the previous compose step, while the second parameter is 10 to indicate that you take the substring after those characters. I copied that from my Flow directly, so it should work but please let me know if you do have issues with it! The third and fourth steps also appear to have their complete parameters – both have a final parameter of 1.

    Thanks,
    Sam

    Reply
      marian June 30, 2020 at 3:37 am

      Thanks for your comments! With your flow as an example I was able to solve my issue with the Stored Procedure!
      I recreated the steps a bit as your examples kept giving me errors. And still step 3 and 4 look the same to me:

      step 3 –> sub(length(outputs(‘Compose_Result_Set_stripped’)),1)
      step 4 –> sub(length(outputs(‘Compose_Result_Set_stripped’)),1)

      I changed it in my flow and it works like a charm!

      Reply
        Sam Stern June 30, 2020 at 4:55 pm

        Glad to hear it is working for you!

        Thanks for pointing out that Steps 3 and 4 were the same – you were definitely right about that. That was supposed to just be step 3, I have updated the blog with the correct step 4 text. Please let me know if you notice any other typos or parts of the instructions that weren’t working for you!

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Receive Posts by Email

Subscribe and receive notifications of new posts by email.