Dynamically Parse JSON as object or Array

[Anonymous]

I have a flow which is getting data from Azure Automation. The result can either be a single object or multiple objects which is represented as array. 

The issue I'm having is; if I use the multiple objects result to build my json schema, it only works for multiple  objects and fails when the result is a single object. Likewise, if I switch the schema to a single object same; it fails whenever I get results in form of array. 

Is there any good way to dynamically switch the schema between object and array ? 

ARRAY SCHEMA

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "From": {
                "type": "string"
            },
            "TO": {
                "type": "string"
            },
            "Subject": {
                "type": "string"
            },
            "IsRead": {
                "type": "string"
            },
            "hasAttachments": {
                "type": "string"
            },
            "RecipientType": {
                "type": "string"
            },
            "RecallStatus": {
                "type": "string"
            },
            "SentTime-UTC": {
                "type": "string"
            },
            "Error": {
                "type": "string"
            }
        },
        "required": [
            "From",
            "TO",
            "Subject",
            "IsRead",
            "hasAttachments",
            "RecipientType",
            "RecallStatus",
            "SentTime-UTC",
            "Error"
        ]
    }
}

Example of SINGLE OBJECT result from Azure Automation

{
    "From":  "jack@domain.com",
    "TO":  "don@domain.com",
    "Subject":  "tester",
    "IsRead":  "False",
    "hasAttachments":  "False",
    "RecipientType":  "domainuser",
    "RecallStatus":  "SUCCESS",
    "SentTime-UTC":  "7/31/2021 6:56 PM UTC",
    "Error":  ""
}

Example of multiple object result (array) from Azure Automation

[
    {
        "From":  "don@domain.com",
        "TO":  "jack@domain.com",
        "Subject":  "natsu",
        "IsRead":  "False",
        "hasAttachments":  "False",
        "RecipientType":  "domainuser",
        "RecallStatus":  "SUCCESS",
        "SentTime-UTC":  "7/31/2021 5:28 PM UTC",
        "Error":  ""
    },
    {
        "From":  "don@domain.com",
        "TO":  "Team@domain.com",
        "Subject":  "natsu",
        "IsRead":  "False",
        "hasAttachments":  "False",
        "RecipientType":  "domainuser",
        "RecallStatus":  "SUCCESS",
        "SentTime-UTC":  "7/31/2021 5:28 PM UTC",
        "Error":  ""
    },
    {
        "From":  "don@domain.com",
        "TO":  "alan@domain.com",
        "Subject":  "natsu",
        "IsRead":  "False",
        "hasAttachments":  "False",
        "RecipientType":  "domainuser",
        "RecallStatus":  "SUCCESS",
        "SentTime-UTC":  "7/31/2021 5:28 PM UTC",
        "Error":  ""
    }
]

  

Thanks for your help..

T

[poweractivate · Accepted solution]

If you know that those are the only two possible schemas, you can have two Parse JSON blocks, one for each schema, hook them to same content for 'Content' field, then use 'Configure Run After' and change it so it is set to when the previous block 'has failed' to try the schema version for a single object 'Parse JSON 2' in case the first one fails for multiple objects schema encountering a single object, and then it will use that fallback block for the single object instead and work correctly.

Here I am using a Compose block, to simulate the input of a single object returned by Azure, at the top of the Flow:

You would then do something like this:

(by the way to access the above options you must click the Ellipses on the upper right of 'Parse JSON 2' and click 'Configure Run After')

Then in the 'Parse JSON 2 should run after' screen, you should uncheck 'is successful', then check 'has failed' and then, click 'Done'.

After you do this correctly,

There should be a red arrow with dashed line, like this:

Note - the schema to use for single object (for the second Parse JSON block) is:

{
    "type": "object",
    "properties": {
        "From": {
            "type": "string"
        },
        "TO": {
            "type": "string"
        },
        "Subject": {
            "type": "string"
        },
        "IsRead": {
            "type": "string"
        },
        "hasAttachments": {
            "type": "string"
        },
        "RecipientType": {
            "type": "string"
        },
        "RecallStatus": {
            "type": "string"
        },
        "SentTime-UTC": {
            "type": "string"
        },
        "Error": {
            "type": "string"
        }
    }
}

I noticed it was not in your original post, so in case, this is the schema to use for the single object off-case.

Then when testing with the single object input, it is successful - failing over from the block expecting an Array, to the other one expecting a single object as you can tell from the green checkmark on the second block below:

The overall result of the flow is successful despite the error - this is because the error is handled using the 'configure run after'

When doing this, you might find that it may seem to be necessary to have the apply to each logic duplicated again. In order to avoid this, you could place the logic for processing each specific individual item, into a child flow, for example, which will then avoid this duplication.

[poweractivate]

@Anonymous 

Or if you prefer a solution with just the one Parse JSON block, it is possible, here is a potentially smarter solution:

In the above, issue a "length" expression compose which will fail if it is an object. In this case, you can concat '[' and ']' to the input in the middle, and use that concat result as the input for Parse JSON, and it will work, I tested it.

In order to do this, you would probably need to initialize and set a variable that is an Array. You would set the variable to the exact input if the length call succeeds (because it is already correct) - but if the length call fails, you would do the concatenation and set that to the variable. 

In order for it to work correctly with variables, you might need to wrap that concat in an array expression to explicitly convert it to an array:

array(concat('[',outputs('Compose'),']'))

I did not specifically test this way with the variables, as it is just a quick alternative that came to my mind that you might like better in case.

[Anonymous]

Thanks guys for the lightning speed response. I went with the first suggestion. I had to make some other modifications downstream cos of the two variables situation it created but got everything working now. When things settle down, I'll try the more efficient method 

[poweractivate]

@Anonymous 

Wanted to give you some more detail about the "smarter method" as it is actually bit more involved to set up - but it could be worth it in case you prefer it, as you would then have only one Parse JSON instead of two. I would recommend you test it though to be sure it works correctly for you.

Here is how you might do it in case you wanted only one Parse JSON action instead of two:

Assuming you have this hard coded input block to test with:

You would basically start with wherever you put your Configure Run After to failed to make the first method to work, and add a parallel branch to add one that runs on success. This would be the branch that runs if the length call does not fail. Here is how to add a parallel branch - click the plus sign in between the steps (in your case, where the red arrow with dashed line is) and then click on "Add a parallel branch", like this:

I'll go ahead and show you how a Flow I tested, which is working for both the single object input and array input with only one Parse JSON action, looks like, and highlight some things to pay attention to following these images:

Then at the very bottom, the Parse JSON action:

Here is how the Flow looks at overall level, above. Following images shows values for each expression:

-

-

For the converging step (Compose 3 in this example) it is very important to have these settings in configure run after, make sure to click on both of the functions in the Configure Run After menu to set both checkboxes. So you should get something that looks like the following in image below if done correctly:

Notice how "Succeeded, Skipped' shows up two times, one for 'Set Variable' and once for 'Set Variable 2' - it must show up twice for it to work correctly. Click on each purple box to set the values for each one. This step should be checked carefully. If not done correctly, one or both branches will never get to the Parse JSON step (so either the Object, or Array, or both, will never get to the Parse JSON step) unless this is done correctly.

If done correctly, here is a test run with the single object version of the Input:

- Notice in the above, it works with the Array Parse JSON, even if the input was just a single object .

And here is a test run for the multi object (Array) version:

You might wonder why is variable String type. When I tested with Array type and used the array formula as I conjectured in the quick post, actually this did not work, that actually wrapped the string inside the array, and did not convert the string to an array . Instead, it worked when I leave this array function out and not do as the quick reply I made conjectured, and instead just have it as a String variable type. The Array branch on the right side will auto convert to string just when setting the variable. The left side produces a String, so this is why the variable should be a String.

As for Parse JSON, it will be able to parse it correctly even if it is a serialized string of JSON rather than an actual Array. I tested by adding an Apply to Each at the end of the Flow on the 'Body' of the Parse JSON, and tested with two elements as the input. It did do two iterations - so it should work.

I tested it and this works, and with this way it is possible to use just one Parse JSON. The one Parse JSON I use in this example is the one for the array schema, by the way. The Flow will leave the stuff intact if it is already an Array (but will convert it to a string - which Parse JSON should still understand correctly by the way). If it is not an array, it will use the concat trick and then save that as a string, and that should also work.

The necessity of it being a String is because we can have variable of only one type in Power Automate. It will work because the Array auto converts to a string on the right side, which is nice. On the left side, that does produce a String. What's also nice is that Parse JSON action block understands a serialized JSON string as the input, which also makes this able to be possible.

I would test just in case if this auto conversion to and from String that Power Automate does, ever hits any issue on some specific data. Using this functionality though, I never encountered any issue to date  that could be attributed to Power Automate turning into a string and Parse JSON turning it back into an Array of Objects, so that functionality is probably working very well. If you are feeling unsure I recommend that you test it in case when using this method.

Check if the above helps and if it results in you needing only one Parse JSON instead of two. In case you like the solution you already implemented based on my first post better, you could continue to use that as well.