JSON operations in MS Flow (Power Automate)

Microsoft Flow (now renamed Power Automate probably because a bunch of other stuff in the world is named "Flow") has a lot of assumptions around JSON built into it.  The "Object" variable in Flow is actually only considered valid if it is valid JSON.  In truth, it should be referred to as a "JSON Object" for clarity.  To support this functionality it does some things behind the scenes that aren't expressly shown on-screen.

Ultimately, Flow automatically escapes out any String variable if you add it to a Flow Object variable.  So you don't have to do squat.  For the most part.  Read on.

NOTE: You might also find useful info within the following post as well: Stupid Power Automate Tricks

Escaping Strings

"Escaping" strings is a common problem in various computer-based text formats.  The problem boils down to:  how do you handle if a value in your text document contains the same values you use to designate formatting?  If you've ever export/imported a CSV from Excel and run into the problem where a particular value happens to have a comma in it (e.g. city/state, Jr/Sr, names with apostrophes, etc.), then you immediately understand the problem.

JSON is simply a formatted text file.  Similar to a CSV, JSON uses certain characters to define where values begin/end.  Unlike CSV, commas are not the problem in JSON.  In JSON, the most common problem is with string values that include double quotes and/or backslashes and "escaping" them is done by precluding them with a backslash (i.e. \" or \\).

For example, if you take a particular string from SharePoint or other external source that could potentially include string items that need to be escaped, then you might assume you need to do some work on those values yourself to fix this.  However, Flow automatically escapes these for you when you include them in a the Set Variable | Object step.

So let's say you have a string value read in from "somewhere" that contains:

• Original:
• This string includes "double quotes" and \backslashes.

If we wanted to assign this value to a string inside a JSON object/document, then you'd need to escape the "'s and \'s by precluding them with a backslash.  Ultimately, we'd need the string to actually look like the following if we were going to assign it to a JSON string name/value pair:

• Escaped:
• This string includes \"double quotes\" and \\backslashes.

What isn't immediately clear is that Microsoft Flow does this for you automatically.  You can see how this works in the following example Flow:

Simple JSON creation in Flow

In this example, I just added the variable directly into this new Flow Object variable (again, which is always  a JSON object).  As a part of dropping a string variable into an Object, Flow automatically escapes any necessary characters (but doesn't tell you is doing that).

Therefore, while you might expect the above to fail, it actually results in:

The last part of the section shows the results

So it took the basic string and went ahead and escaped it out for you.

I do appreciate Flow doing this on your behalf, but this is one of those scenarios when your own distrust of external data (a very correct way of viewing things) could cause you to overreact and cause more problems than you will solve.

Manually building can be cumbersome

If you tried to do the above instead manually by using the Expression Builder on the Set Variable step then you might not have much success.  For example if you tried the following it would fail:

• json( concat( '{ \"testString\": \"', variables('testString'), '\" }' ))

The concat() string it would assemble (using the above example value for testString) would be:

• json(  '{ "testString": "This string includes "double quotes" and \backslashes." }' )

When it should look like:

• json(  '{ "testString": "This string includes \"double quotes\" and \\backslashes." }' )

This means that you'd need to manually escape the variable testString (using multiple replace() functions) BEFORE you attempt to merge everything.  Which is notably worse..

However, there are other use-cases where an alternative approach might apply.

Populating/altering it after creation

If you attempt to use the addProperty() or updateProperty() Expression Builder functions, then the escaping does work.  You don't have to do anything further to the string.  This means that you can also easily add/modify values within a JSON object after it is initialized.

However, the caveat to this is that variable assignments cannot be self-referential.  Meaning, if you tried to do a SetVariable step for a JSON object, then you couldn't use that same object within an assignment/function inside it.  Since the add/updateProperty functions take the object name as an input:

• SetVariable | testJSON | addProperty(testJSON, 'name','value')

You wind up getting an error.

• The inputs of workflow run action 'Set_variable' of type 'SetVariable' are not valid. Self reference is not supported when updating the value of variable 'testJSON'.

To belabor the point, attempting to "Set" the value is actually redundant as the two functions above actually do the assignment for you.

So how do you run these?

The answer: use the Data Operations | Compose step inside the Expression Builder after you do an Initialize Variable step as the example above.

By performing the following functions within two Compose steps...

• addProperty(variables('testJSON'),'newValue',variables('testString'))
• updateProperty((variables('testJSON'),'newValue','2nd Value')

...inside the Expression Builder (you can't just copy/paste these into the steps themselves)...

...you'd get the results included below:

A little confusing per results but see the explanation below

Now, the results do look a little strange because we're actually running the commands (i.e. addProperty() and setProperty()) as a part of the Input so the Input/Output both hold the results of this command.  However, you can see that the new item 'testString2' is added in the first step (using the same value as the original assignment to show escaping works) and then updated to a different value in the 2nd.

It's possible that there exists a simple function to escape a string in a single step, but I haven't come across it yet.  This all seems to happen automatically when directly adding a string to a JSON object.

Troubleshooting/Fixing Issues

Since JSON is very flexible, we should be prepared for issues that occur in the middle of production.  Failed submissions done through Power Automate can be resubmitted once you've fixed your code.  So if you figure out how to parse the JSON better and/or fix the issue w/ previous submissions, you can fix your Power Automate script and then resubmit your failed submissions.

There are two big areas where JSON inputs commonly cause you heartburn inside Power Automate.

Missing Required Data 

There is a step that makes things "easier" called the ParseJSON step.  It allows you to map out JSON samples into references that Power Automate can then use easily within other steps.  However, it can also get you into unexpected trouble.

One item you might bump up against in trying to use the ParseJSON step is that once you've created this step through some sample JSON it makes all of your variables required.  So if you use this step, then consider checking this section of your step and removing anything that isn't actually required:

You can remove anything that isn't required from the "required" section above.

Fixing Nulls

Also, if you go down the road of using this step then you might also get in trouble if you send in a NULL value for anything.  Now clearly, you shouldn't do that.  But let's say that you're in production and you notice it and suddenly have to do a quick fix, then you can punt and do something like this:

The Value field here is an Expression that is actually a replace() function to replace the string 'null' with a valid value.  For example, if we passed in a string of JSON (the only way you can pass this in currently) to Power Automate via your trigger, and you had a string value that was occasionally passing in a null, then you can do something like:

replace(triggerBody()['text'],'null','"none"')

This turns the following:

    {

        "ID": 19977,

        "groupDesc": null,

        "groupNum": "30125",

        "groupType": "Department",

        "keepAccess": "Y"

    }

Into:

    {

        "ID": 19977,

        "groupDesc": "none",

        "groupNum": "30125",

        "groupType": "Department",

        "keepAccess": "Y"

    }

This works because the JSON value passed in originally is itself a big string.  So we can use string functions to alter it before we allow the ParseJSON step to touch it.  Now, if you had multiple nulls coming in and some were Strings and some were numbers, etc. then of course you'd have to be a little more strict on your match in your replace() function above.