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!

[adrianoc]

We have fed back internally through the partners we're working with on Microsoft Fast Track, but I wanted to reiterate here that this ParseJson function, even in its experimental form, is a huge improvement over existing patterns for consuming data from external APIs for display/processing in the app.

We have an app that is designed to allow users to search a third party API, and then that data can be used to fill out details on other records. Previously we used a Flow to call the API and the flow created records in a custom Dataverse table, and then we displayed the results in the app directly from table with a filter. That was extremely slow, mostly because it just took a long time to process the steps in the Flow. Returning the API response directly and displaying it with Table(ParseJson()) gave a massive boost to performance.

I will say that the syntax feels a little obscure/strained to me, especially with lots of nested JSON from a Graphql query (for example) needing chained Index()es , but I am not a Canvas App expert -- this is the first time I've ever seen Canvas Apps -- so this feeling might be my lack of understanding of the design goals for this tool. I notice you've updated this post to say that the type casting requirements have been improved, but I actually didn't mind the explicit casts, I felt they made it a little clearer to scan what was going on in the data.

I would love to see this function move out of experimental and into preview, we'd very much like to use this feature more widely.

[rodneyb]

This feature opened up a whole range of capabilities for me as I use a single PCF to request commands and return json.

One blocker that has come up just now is what seems to be this: If the ParseJson call used to populate a collection, it does not appear to be completed when, say, a Navigate is called immediately after. I get the collection but only one row with the value "true" populated in a new column called "Value". If I eliminate the navigation, it builds the collection fine (I've been using ParseJSON quite extensively for the last month).

Is the ParseJson in fact an asynchronous operation? Thanks, Rodney

P.S. This issue only shows up after publishing and viewing on an android tablet or phone. Editing or playing on the desktop does not exhibit the issue (not sure about apple).

[JorisdG]

Are you using Collect() or ClearCollect() to populate the collection? And on mobile you're using the main Power Apps app I assume?

[rodneyb]

Yes, ClearCollect. I'm not sure what you mean when you mention "main Power Apps app". Do you mean as opposed to a direct url?

As an aside, I have noticed that most connectors or actions seem to be asynchronous. Unless, for example, I check for, say, the result of a Flow. Then the code waits and I'm able to check result success. In fact, to counter that behavior when I want speed but need stuff to wait on other stuff, I created a queue system so Flow calls (and results checked) happen in a separate thread.

Here's the code just before I call Navigate. The next screen's data call only works (99% of the time) because, on the next screen I implement a 200ms delay with a Timer. It's notable, too I think, that, without the Timer or any other hack, the data on the next screen will load correctly every time after the first navigation back/forth. As if it needed a one-time cache to load:

      ClearCollect(collAllProductsZASSLocations, 
        ForAll( ParseJSON( actionslocationsdata ) , 
        { 
            sProductName: Text(ThisRecord.Value.sProduct), 
            sCount: Value(ThisRecord.Value.sCount),
            iProductID: Value(ThisRecord.Value.iProductID) 
        });
        //UpdateContext({startDelay:true})
         //Notify("naving", Success, 4000);
         Navigate(screenAllZASSLocations)

Let me know if that doesn't make sense.

Thanks, Rodney

[PavelSheludkov]

Just gave this feature a go while evaluating an SQL On-Prem integration scenario.

It works well and allow to easily parse flow response that running a stored procedure to obtain required data.

Any updates on when ParseJSON feature is going to move to Preview?

[LaelLeeH]

Hi @JorisdG 

I have until now been using Match and MatchAll to manually parse json strings. I recently started swapping over to ParseJSON and so far so good, but I am now battling with a nested JSON array of records.

My Match statement was:

 ... where the last field ProcessPerformersJSON, is a nested JSON array of records which I Parse at a later date into another collection

My new ParseJSON looks as follows:

But that last conversion is throwing an error:

I have tried replacing Text(ThisRecord.Value.ProcessPerformersJSON) with  Table(ThisRecord.Value.ProcessPerformersJSON) and with Text(ThisRecord.ProcessPerformersJSON) and with an entire ForAll to Parse the nested table and I can't get anything to work. 

I can send you a copy of my incoming JSON string privately if necessary.

Any assistance you can provide will be greatly appreciated.

Thanks

Regards

Lael

[LaelLeeH]

Hi @JorisdG 

I have another scenario where I have been using string manipulation up until now, and it works perfectly but it is ugly and I am sure it is not too performant either.  I would love to replace it with ParseJSON if possible.  However, the JSON field name is dynamic (or passed in to the string manipulation as a parameter) and I am not sure if this is possible with ParseJSON.

My current manipulation looks something like this. See InputValue: in the code below:

ClearCollect(

            colFormFields,

            AddColumns(

                        colMyHeaderAttributes,

                "InputValue",

                                                    Mid(

                                                        First(

                                                            Split(

                                                                Mid(

                                                                    _lSelectedTransaction.jsonCol,

                                                                    Coalesce(

                                                                        Find(

                                                                            """" & jsonName & """:",

                                                                            _lSelectedTransaction.jsonCol

                                                                        ),

                                                                        99999999

                                                                    ),

                                                                    Len(_lSelectedTransaction.jsonCol)

                                                                ),

                                                                ","""

                                                            )

                                                        ).Result,

                                                        Find(

                                                            ":",

                                                            First(

                                                                Split(

                                                                    Mid(

                                                                        _lSelectedTransaction.jsonCol,

                                                                        Coalesce(

                                                                            Find(

                                                                                """" & jsonName & """:",

                                                                                _lSelectedTransaction.jsonCol

                                                                            ),

                                                                            99999999

                                                                        ),

                                                                        Len(_lSelectedTransaction.jsonCol)

                                                                    ),

                                                                    ","""

                                                                )

                                                            ).Result

                                                        ) + 1,

                                                        (Len(

                                                            First(

                                                                Split(

                                                                    Mid(

                                                                        _lSelectedTransaction.jsonCol,

                                                                        Coalesce(

                                                                            Find(

                                                                                """" & jsonName & """:",

                                                                                _lSelectedTransaction.jsonCol

                                                                            ),

                                                                            99999999

                                                                        ),

                                                                        Len(_lSelectedTransaction.jsonCol)

                                                                    ),

                                                                    ","""

                                                                )

                                                            ).Result

                                                        ))

                                                    )

jsonName  refers to a field in colMyHeaderAttributes, and so is dynamic per record being added to the collection

Also, _lSelectedTransaction.jsonCol is not a unique json structure.  I have multiple different 'Transaction' types all configured with different jsonCol field naming structures, and hence the need to look up the jsonName in colMyHeaderAttributes

I have been trying to replace the above with something like this:

InputValue : Text(ParseJSON(_lSelectedTransaction.jsonCol).@jsonName)

or 

InputValue : Text(ParseJSON(_lSelectedTransaction.jsonCol).colMyHeaderAttributes[@jsonName])

Obviously both fail, and I totally understand why.  Is what I am trying to do possible by some other means?  It will make a massive difference to my app if I can get it to work. 

Thanks, in the sincere hopes of a successful outcome 😉

Regards

Lael

[LaelLeeH]

Hi @JorisdG 

Are you able to have a look at my two issues below and respond.  Getting them working will allow me to make some significant performance improvements to my app which are very sorely needed.  Will really appreciate a response.

Many thanks

Regards

Lael

[JorisdG]

Sorry for the late reply. Unfortunately we don't have a way to access a property by its name dynamically. It's something we've discussed in the past but we have no designs or anything on the roadmap at the moment.