ParseJSON feedback

[JorisdG]

It’s been roughly two months since we released ParseJSON as an experimental feature in Power Apps. A lot of scenarios for makers have been unblocked with this feature, and we’ve seen great uptake even though it’s still considered experimental. We’re anxious to get this out of that experimental status. I wanted to take the opportunity to highlight a recent improvement, and address feedback with some of the future plans we have once we take ParseJSON (and untyped objects) out of the experimental phase.

Finally, and most importantly, I’m asking for your feedback. I will elaborate below but if you’re using ParseJSON I would love to hear from you. We’ve seen comparatively low feedback given the amount of usage. This is probably a good sign, but it makes us a little nervous pushing to preview or GA without making sure there aren’t any issues we just haven’t heard about.

Update since experimental launch

You can now use ForAll(), like Index(), directly on an untyped object. A JSON object array such as [ { “id”: 1, “Title”: “One” }, { “id”: 2, “Title”: “Two” } ] would previously require a Table(ParseJSON()) call before going to ForAll. This has the added drawback of resulting in a single-column table with untyped objects, so you would get something like:

ForAll( Table(untypedObject), { id: Value(ThisRecord.Value.id) )

requiring you to use the .Value field on ThisRecord for the single-column table. You can now pass untyped objects directly into ForAll, which no longer creates a single-column table requiring the use of the Value column. So the previous example becomes:

ForAll( untypedObject, { id: Value(ThisRecord.id) )

Feedback so far

The feedback we’ve received so far is positive in that this feature unblocks many scenarios involving JSON. There are two recurring points of feedback we’ve heard from multiple customers:

1. The feature is very “verbose” today due to the requirement of casting every field individually to a specific type. If you set a table of untyped objects as the Items source of a gallery, each use of a field requires Value(), Text() and other casting everywhere you wish to use the fields. For now you can consider using ForAll to type everything into a typed table first, as shown above, making field access easier. Our ultimate goal is to allow you to define your own type schemas and cast the whole JSON to it. We envision this feature to be used with the ParseJSON() function, where you pass in the JSON string as well as the type you defined, and the result of ParseJSON() would then be a typed object as opposed to an untyped object that requires casting. To be clear, we intend to GA ParseJSON with untyped objects first, and add the new typing features later on.

1. Using ParseJSON() with connectors. If a column from a datasource contains a string with JSON, that is easy enough. But we’ve heard of scenarios where makers want to take the whole response message from a connector and manually parse the JSON when the app runs. The connector team is looking to build on top of our work with untyped objects, and beyond that also the previously mentioned type schema definitions to take connectors to a new level. The experimental Dynamic Schema feature is great but is missing some features and flexibility in Power Fx, and we’re starting to address those in Power Fx (with untyped objects and type definitions). There’s some short and long term work going on that I hope you will get to hear about soon.

If you have any feedback or concrete scenarios you’d like to share, I'd love it if you could comment on this thread. I would love to understand how you’re using ParseJSON or trying to use ParseJSON!

[biterbit]

@Zanzaid1 I think it might depend on whether the app has been updated with a newer version. I found that apps that I had not changed recently could access the earlier version, but those that had been updated with later versions could not. So maybe some of the later versions introduce changes that cannot be reversed, so you pass a sort of invisible 'point of no return'.

[VTBob]

Any update on begin able to ParseJSON() directly from a connector?  This is driving me nuts.  I have the JSON but I can't do anything with it (can't parse it) because it is untyped.  Are there any workarounds??  I dump it into a variable but it is untyped so I can't ParseJSON it.  I can look at the variable value in Power Apps editor, copy it, paste it into a text box, and then ParseJSON the text box, but I can't ParseJSON the connector or variable because they are untyped.

Maybe the only thing to do is Parse it with Power Automate?  Not excited about the lag...

[J_Taylor]

@VTBob, I'm not an expert, but you need to manually convert to the appropriate type. There is a guide somewhere in this thread. I have had over ten fields that I had to manually do myself (although ChatGPT would be helpful). It looks something like this: 

ClearCollect(colSitePages, ForAll( Table( ParseJSON(varJSONPages) ), { id: Value(ThisRecord.Value.id), Title: Text(ThisRecord.Value.title), FileRef: Text(ThisRecord.Value.fileref), CanvasContent: Text(ThisRecord.Value.canvascontent) } ) ) )

[JorisdG]

What exactly are you trying to do? We have added automatic coercion for most scenarios quite a while ago, so you shouldn't even have to convert them to proper types. You can't get intellisense for them because they're untyped and undiscovered during development of your app, but everything should work. ParseJSON() takes a string and returns an untyped object, so that's not what you're looking for anyway.

Can you elaborate what you're trying exactly?

[VTBob]

I have a connection that returns a JSON string; or, actually, I guess it's an untyped object. I want to put the JSON data from the connection into a table, but I can't because it's not a string, it's an untyped object. 

[JorisdG]

Gotcha. In that case yes you will need to explicitly convert to typed fields... for example if you need to make a new record you would do something like:

Set( myNewRecord, { ID = Value(untypedjson.ID), Name = Text(untypedjson.Name) } )

In other areas you can use untypedjson.ID directly as Power Fx can know what type is expected and automatically convert, for example when you set it in a label. When you're trying to put it in a record things need to match perfectly so you need to explicitly convert them.

Also check out https://learn.microsoft.com/power-platform/power-fx/working-with-json 

Hope this helps...

[Ryanbell]

I'm currently testing using ParseJSON to replace my Split/Concat method of storing a table in a multi line text column in SharePoint.

Reading the data is easy, manipulating and patching back was a bit of a struggle, but found a solution. Not sure if it's the best way, but seems to be working well.

By creating a collection of the Parsed JSON with the AddColumns:

ClearCollect(colMechanical,AddColumns(Table(ParseJSON(varSelected.Mechanical)),
"Row",Text(ThisRecord.Value.Row*1),
"Item",Text(ThisRecord.Value.Item),
"Section",Text(ThisRecord.Value.Section),
"Comments",Text(ThisRecord.Value.Comments),
"UOM",Text(ThisRecord.Value.UOM),
"Type",Text(ThisRecord.Value.Type))));

I can then manipulate the Collection and patch it back to SharePoint using the IgnoreUnsupportedTypes to ignore the original UntypedObjects.

Patch(Database,LookUp(Database,ID = varSelected.ID,ThisRecord),
{
Mechanical:JSON(colMechanical,JSONFormat.IgnoreUnsupportedTypes)
});

Not sure if this is the best method of doing it, but it feels better than having to use ForAll and Split to reconstruct collections.

[JorisdG]

You could also do ForAll( ParseJSON(..), { Item: Text(ThisRecord.Item), UOM: ..... } ) etc.

[takolota]

@JorisdG 

Thank you & the team soo much for adding this functionality. I have a couple example use-cases you may be interested in…

Large SharePoint List Delegation Eorkaround

https://powerusers.microsoft.com/t5/Community-App-Samples/Large-SP-List-Delegation-Workaround-App/td...

This solution using ParseJSON helps resolve a Lonnggg standing issue common among users who couldn’t get the best functionality out of apps using SharePoint lists because of delegation limitations. It enables filtering, searching, & returning a lot more SharePoint items beyond the set 2000 delegation limit. It can even search multiple multiline text columns.

Secondly, I am using ParseJSON( ) to return JSON data extracted from many different documents with different formats from different suppliers from this flow template: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Extract-Data-From-PDFs-and-Images-With-G...

I find it is much more flexible, lower maintenance, & lower cost than other AI Builder methods in extracting data from many different documents.

So I actually put this all together in my app by returning the flow JSON as a string to a variable and an input field for each document, ex: InvoiceDocumentJSON. That way I can track which documents have been uploaded & data extracted during the current user session & still have the string JSON of past document extractions for files in previous user sessions. Then when the app registers a new document has been uploaded, it performs several comparisons on many fields & table rows to set several Yes|No|N/A fields to Yes or No. Once implemented it will effectively automate at least half the order document cross-checks for our procurement team.

The system effectively reads documents & completes most of any given review on its own!

[TxH]

It seems that ParseJSON() currently behaves differently in preview mode than in play mode.

Steps to recreate the issue:

1. Create a new Canvas app and add the "Accounts" table to it

2. Add a button to the screen and rename its caption to "Init"

3. Add the following code to OnSelect():

Clear(colData);

// load accounts into collection as stringified json
ForAll(Accounts,
    Collect( colData, {
        id: CountRows(colData) +1, 
        entity: "account",
        record: JSON(ThisRecord, 
                    JSONFormat.IgnoreUnsupportedTypes &
                    JSONFormat.IncludeBinaryData)
    })
);

4. Add a Text Label, set its BorderThickness to 1 and add the following code to the Text property:

Coalesce( Text( ParseJSON( First( Filter( colData, entity = "account")).record).name), "empty")

5. Preview the app and click the Init-Button

The first account name will be shown

Up to here the app behaves as expected.

6. Save the app and close the edit window

7. Play the app (in make.powerapps.com, Windows App or on Mobile)

8. Click the Init-Button

The Text Label shows "empty"

I have no idea why the app behaves this way in play mode. I have tested in 2 different tenants with different versions (3.23091,14, 3.23092.4, 3.23082,20).

---

LATER NOTES:

After further testing I found out that the cause for this misbehavior has nothing to do with the ParseJSON(), nor the JSON() function, but with the app setting Explicit column selection.

If this setting is enabled (which is the case by default), only the columns specified in the code are transferred from supported data sources.

For the following code example only the accountid column is transferred in play mode, since no column is explicitly addressed.

Clear(colData);

// load accounts into collection as stringified json
ForAll(Accounts,
    Collect( colData, {
        id: CountRows(colData) +1, 
        entity: "account",
        record: JSON(ThisRecord, JSONFormat.IndentFour & JSONFormat.IgnoreUnsupportedTypes & JSONFormat.IncludeBinaryData) 
    })
);

 

 However, this only applies in play mode. In preview mode, the entire records will be transferred.

 

If the app setting Explicit column selection is deactivated, the entire record is transferred, even if no specific columns are addressed.

Why it behaves differently in preview mode than in play mode is beyond me. To avoid future confusion, I think the  preview mode behavior should be aligned with the play mode.