In this article, we’ll explore what JSON is and what it means to ‘parse’ JSON. We’ll then delve into the related functions that are available in Power Fx and how they might be of use.
Both functions have worked their way into the canvas app I’m building for my wife’s business, so felt they deserved some air time!
JSON stands for ‘JavaScript Object Notation’. It is a lightweight data format that’s easy to read and write, whilst also easy for machines to parse and generate. JSON is commonly used for transmitting data between a server and a web application, as well as storing and exchanging data within different systems or API endpoints.
Here are a couple of key points to understand about JSON in the Power Platform:
Structure: JSON data is represented as key-value pairs, like dictionaries or objects in other programming languages. Each key is followed by a colon, and values can be strings, numbers, boolean values, arrays, or nested JSON objects.
Syntax: JSON uses a specific syntax with curly braces ({}) to enclose objects and square brackets ([]) to define arrays. Objects consist of comma-separated key-value pairs, while arrays contain multiple values separated by commas.
Usage: JSON is used extensively in the Power Platform and across other areas of Microsoft 365. Sometimes you won’t realise it’s the driving force behind the scenes, as Microsoft have given us user-friendly layers or wrappers to interact with.
For example, you may add a ‘Send an email’ action to Power Automate which is super easy to configure:
Under the surface it’s calling an API and returning the response in JSON format:
JSON example
In a previous post, I used the weekly food shop for an example. Let’s use the same example to represent a JSON object:
{
"week": "June 12th 2023",
"items": [
{
"name": "Apples",
"quantity": 5,
"category": "Fruits"
},
{
"name": "Bread",
"quantity": 2,
"category": "Bakery"
},
{
"name": "Milk bottle",
"quantity": 1,
"category": "Dairy"
},
{
"name": "Chicken breast",
"quantity": 3,
"category": "Meat"
},
{
"name": "Eggs",
"quantity": 12,
"category": "Dairy"
}
]
}
The example has two key-value pairs:
The ‘week’ key has a string value of the date of purchase.
The ‘items’ key has an array as its value, representing the list of items purchased during that trip to the supermarket. Each item in the array is represented as an object with its own key-value pairs for ‘name’, ‘quantity’ and ‘category’.
If we look at each item in the ‘items’ array, we can see that we purchased 5 apples, 2 loaves of bread, 1 bottle of milk, 3 chicken breasts and 12 eggs.
If you struggle to read and understand JSON arrays, there are plenty of useful tools you can use. There’s a section at the end of this article that list a few good ones. For now, we’ll use my favourite – JSON Crack – to visualise our weekly shop JSON object:
Cool, so what is Parse JSON?
To parse JSON means to extract and interpret data stored in a JSON format. When you parse JSON, you convert the JSON data into a structured format that your Power Platform solution can understand and work with. This is a very common technique & terminology across a variety of programming languages, for example JavaScript.
Parsing JSON involves a couple of steps:
Reading the JSON: You read the JSON data, which is typically stored as a string or retrieved from an external source like an API.
Parsing the JSON: The Power Platform provides different methods to parse JSON data. These methods take the JSON string as an input and converts it into a suitable data structure.
Accessing the parsed data: Once the JSON is parsed, you can access the data using the appropriate method. This allows you to retrieve specific values, iterate through arrays, or navigate through nested objects.
Parsing JSON is essential when working with JSON data within the Power Platform, as it enables you to extract and utilize data in meaningful ways.
We’ve long had the ‘Parse JSON’ ability in Power Automate, however in Power Apps it’s historically been a bit trickier. That is, until the release in 2022 of the ParseJSON Power Fx function.
Power Fx functions
Let’s look at the related Power Fx functions in a bit more detail.
JSON function
This function takes a table structure and provides you with the JSON equivalent, so that it’s suitable for storing or sending. The function can only be used with behaviour properties, by that I mean something like an OnSelect or OnChange property. We can’t solely use the JSON function in things like labels or the Items property of a gallery:
The Power Fx syntax for the JSON function is as follows:
JSON(NameOfYourCollectionOfData, Format)
Whilst optional, the ‘format’ part is important to know about so you pick the correct option for your needs.
Compact: This is the default format, so if your Power Fx is JSON(NameOfCollection), it will automatically use Compact as its format. Compact has no added spaces or new lines.
IndentFour: This option visually makes the output look better, as adds lines and breaks in to compartmentalise the text.
IncludeBinaryData: This option converting of any images, videos & audio clips. Use with caution though, those file types aren’t small, so converting them will have adverse performance affects.
IgnoreBinaryData: The opposite of above – won’t include any image, video or audio outputs.
IgnoreUnsupportedTypes: This option will ignore any data types that are not supported in this list. If it finds any, it’ll throw an error.
Visually, there isn’t much difference between any of them in a canvas app interface.
As an example, I’ve put the weekly food shop information into a collection. I’ve used the JSON function to convert the content to a JSON string. For Compact, Ignore Binary, Ignore Unsupported & Include Binary, the visual output is the same. There’s no additional line breaks or structure:
Indent Four is the only format option that give a more user-friendly, more common visual output:
ParseJSON function
As per Microsoft’s initial guidance, “the ParseJSON function will parse a valid JSON string and return an untyped object representing the JSON structure”.
So what’s an untyped object? When the function was initially released, ParseJSON translated a JSON string for us but it didn’t really have a clue what type of data each field was. We therefore had to convert each field manually into the type we needed, ie text, colour or boolean. Microsoft documented what conversions we needed to do and how.
In the example below, we see what this would look like previously. Note the ‘Text’ conversion for each field as we need to give each untyped value a data type:
ForAll(
ParseJSON(MyJSONString),
{
servername: Text(ThisRecord.servername),
dbname: Text(ThisRecord.dbname)
}
)
Since then, Microsoft have made some improvements to this experimental function. Where possible, the untyped conversions will be done for us, meaning we now write a bit less code. This won’t work all the time though; if there are multiple overloads with different data types, you may still have to do the explicit conversions manually.
Using the same example as above, we see what this would look like. Note the ‘Text’ conversion for each field is no longer needed:
ForAll(
ParseJSON(MyJSONString),
{
servername: ThisRecord.servername,
dbname: ThisRecord.dbname
}
)
Power Fx examples
I’ll go over a few examples below for how we can leverage these functions in the Power Platform. Please note, the ParseJSON function is currently in Preview. Microsoft don’t recommend we use preview features in Production solutions, so be wary before committing to using it.
That said, let’s walk through how to enable it as it’s off by default.
Open Power Apps in edit mode & click ‘Settings’ from the top ribbon menu:
From the resulting popup, select ‘Upcoming features’ and search to find the preview feature of ‘ParseJSON function and untyped objects’:
Get value from JSON environment variable
I referenced these in my previous post, along with how to retrieve and use the values in Power Automate and Power Apps. We’ll use the same example – using a JSON environment to store information about a SQL Server instance, but we’ll use the ParseJSON function for retrieval rather than Power Automate. The same examples can work with any other JSON source, such as from a column in a table or passed in from another data source.
Please note that we’ll be using the following Dataverse tables, as such any user using our app would need a premium license:
We can begin by adding a label to test that we can retrieve the value from the environment variable. For that, we use the following code:
LookUp(
'Environment Variable Values',
'Environment Variable Definition'.'Schema Name' = "NameOfEnvironmentVariable"
).Value
That’s great, but it’s returned a string. we need to retrieve the values for each of the 2 columns in the JSON object – servername and dbname. As such, we’ll need to use methods to create or view the data in a table-like format.
Creating a collection
To store the output in a collection, we can use ParseJSON to iterate through our value in the environment variable. For each field it finds, we can map it to columns in a collection.
My preference in this example is to use a With() function to store the value retrieved from the lookup. Then, with that output I’ll build a collection. This makes the collection build cleaner and easier for me to read, as the lookup isn’t nested deep inside other functions.
I’ll add a button to my app and add this Power Fx to the OnSelect property:
With(
{
tvEnvVarValue: // name of temporary variable inside our With function
LookUp(
'Environment Variable Values',
'Environment Variable Definition'.'Schema Name' = "NameOfEnvironmentVariable"
).Value
},
// With the output of the With() function, create a collection
ClearCollect(
colCollection,
ForAll(
ParseJSON(tvEnvVarValue), // referencing output of above With() function
{
servername: ThisRecord.servername,
dbname: ThisRecord.dbname
}
)
)
);
With the collection now built, we can add a gallery and use it in the Items property. We can then add 2 labels and set their Text property to ThisItem.servername and ThisItem.dbname:
Using a Gallery
If we didn’t need to store the data in a collection and just wanted to visualise, we can cut out the middleman. I’ll add a gallery to my app and add the following Power Fx to the Items property:
With(
{
tvEnvVarValue: // name of temporary variable inside our With() function
LookUp(
'Environment Variable Values',
'Environment Variable Definition'.'Schema Name' = "NameOfEnvironmentVariable"
).Value
},
// With the output of the With() function, do the thing
ForAll(
ParseJSON(tvEnvVarValue), // referencing output of above With() function
{
servername: ThisRecord.servername,
dbname: ThisRecord.dbname
}
)
)
Getting a field and its value
If you don’t need to store the whole output as a table, you can just hone in on the individual row, field and value.
With our environment variable, our JSON output is a table/object with 1 row. The example formula below is easily customisable to work with multiple rows. I’ve added a label control & added this to the ‘Text’ property:
With(
{
tvEnvVarValue: // name of temporary variable inside our With() function
LookUp(
'Environment Variable Values',
'Environment Variable Definition'.'Schema Name' = "NameOfEnvironmentVariable"
).Value
},
/*
With the output of the With() function, use the Index function to retrieve line 1 (the first line), to
then get the servername field from the returned value of the Index
*/
Index(
Table(ParseJSON(tvEnvVarValue)),
1
).Value.servername
)
In our example, that returns the relevant servername value of ‘dev.database.windows.net’:
Saving and retrieving child records
Sometimes we might have a parent record with one or more associated child records. In Power Apps we’d typically handle this with 2 tables, with the parent record being stored in one and the child record(s) in another. There’d be a unique identifier that’s shared between the 2 sets of records for retrieval and edit purposes.
This is a perfectly fine method and one I’ll continue to promote. That said, I also like to find different ways of doing things! In this example then, we’ll use the JSON function to convert child records into a string, then save it with the parent record. We can then use the ParseJSON function to retrieve that data back. Let’s bring it back to my wife’s business, as this is how we’ll be handling the relationship between a client’s appointment & the relevant treatment(s) they’ve chosen.
We start with a simple dropdown to select the relevant client from our database:
We then need a list of treatments, their relevant duration and price. Clients can choose a tailor-made package for the relevant appointment, consisting of one or multiple treatments. I’ve got a collection called colTreatments:
ClearCollect(
colTreatments,
{
Treatment: "Leg wax", Price: 20, Duration: "15 mins"
},
{
Treatment: "Spray tan", Price: 35, Duration: "10 mins"
},
{
Treatment: "Eyebrow shape", Price: 10, Duration: "10 mins"
},
{
Treatment: "Fancy nails", Price: 50, Duration: "45 mins"
},
{
Treatment: "Even fancier nails", Price: 500, Duration: "75 mins"
}
)
I’ve added a gallery and added colTreatments to the Items property. On the OnSelect property of the arrrow icon, I want to add the row to a new collection:
I’ve added a couple of treatments to my chosen package, I’m now ready to save the data (I can’t WAIT for my spray tan, EXCITING!!!):
Saving the data as JSON
I’ve added a button and configured the OnSelect property with the following Power Fx, which will:
1) Using the JSON function, convert the selected treatments collection (colTreatmentPackage) into a JSON string.
2) Using Patch, create a new row in the Appointments table. The JSON string is saved as part of this, into a Multiple Line of Text field.
3) If the Patch fails, show an error notification, else
4) If the Patch was successful, show a success message and empty the the colTreatmentPackage collection
// We do IfErrors around a Patch because it's best practice ;)
IfError(
Patch(
// Create new record in Appointments tbl
Appointments,
Defaults(Appointments),
/*
Client name as per dropdown
Treatments collection converted to JSON, output then stored
in Multiple Line of Text field type in Appointments tbl entry
*/
{
ClientName: dpdClientName.Selected.Name,
Treatments: JSON(colTreatmentPackage)
}
),
// Tell me if it's gone Pete Tong
Notify(
"Patch failed. Please try again",
NotificationType.Error
),
// Otherwise, tell me that it's worked like a boss & empty the collection
Notify(
"Patch worked. AWESOME!! Go and do some fly kicks",
NotificationType.Success);
Clear(colTreatmentPackage)
);
Success! The new record has been successfully added, you can see the collection has converted to JSON with the output saved as part of the record:
Retrieving the JSON data
To view the data, we can use ParseJSON to take that saved JSON string and blow it back up into a collection.
I’ve added a gallery, with the Items property set to my Appointments table. The created entry is there and the JSON string clearly visible:
On the ‘View’ icon, I’ve configured the following Power Fx on the OnSelect property. As a reminder, we no longer need to declare the type – the function should automatically detect that Treatment & Duration are text and Price is an integer:
// Create collection with saved JSON string
ClearCollect(
colSelectedTreatments,
ForAll(
ParseJSON(ThisItem.Treatments),
{
Treatment: ThisRecord.Treatment,
Duration: ThisRecord.Duration,
Price: ThisRecord.Price
}
)
)
Sorted, JSON string converted back to a collection via the ParseJSON function:
Cool JSON tools
I’ll finish this article by recommending a few nifty JSON tools.
I’ve mentioned JSON Crack before; it’s easy to use, visualizes structures really well and can validate any JSON you’re building. Super useful if you have an API response with millions of nested objects. it can help you find the field(s) you want and where they are. I’ve found this useful for querying outputs in Power Automate.
If JSON Crack doesn’t do it for you, maybe try JSON Hero or JSON Formatter & Validator.
If you use Microsoft Edge, there’s an experimental browser extension from version 110 onwards. Details and how to add are here. I’ve also found this very helpful.
Since this post went live, Dawid Ziolkowski reached out and sent over his blog on the same topic from last year. In it, he explains a faster and more performant way of using the ParseJSON function. Go check it out here.
I’ve also submitted a sample app to the Power Platform Samples Solution Gallery, that covers some of the topics covered in this post and gives some examples. You can download the solution and get hands-on with the concepts.
Click here to access the sample. Any feedback, please let me know!
Brilliant Post, Craig. Its strengthened my understanding of JSON/PaseJSON. I’ll try and replicate your wife’s example just to reinforce the knowledge. Nothing like actually doing it. Thanks for sharing
Thanks Douglas, glad the article is able to help. ParseJSON is a game changer for Power Apps, can’t wait for it to come out of Preview so can use in Production apps for clients.
Thanks for the awesome write up! I just started getting into working with JSON in PowerApps and this helped a lot. I haven’t read the other blog you referenced but I’m curious, let’s say I need to update a value in an array, from an Orange to an Apple. Is that possible to do through the power app?
I was able to get a collection of JSON and patch it to a list, return it to a gallery and then return the individual items of the array but I got stuck on how I would then update. Any help would be appreciated but thanks again!
Hi Jordan,
Great question – and one I’ll be covering in a future blog post (probably around w/c 31st July).
In your gallery, you’d need to add the relevant controls – let’s say a dropdown or a text input control. You can then use these controls to update the underlying collection, so when you convert to JSON & update the row in your data source it’ll reflect the changes.
You’d need to set the Default property of the controls to the relevant value in your collection – so ThisItem.Quanity etc. You’ll also need to configure the OnChange property to perform an update to the underlying entry & field(s) – I typically do this with an UpdateIf function, so you might have something like:
UpdateIf(
colCollection,
LineID = ThisItem.LineID,
{Quantity: Value(TextInputControlForQuantity.Text})
If doing this with Text Input controls, it’s also worth setting the DelayOutput property to True.