create collection from a string containing JSON

[MichelH]

Hello all,

I'm trying to understand why I cannot create a collection from a string/variable that contains the same JSON that I can use to create it in the code.

So this will create a collection with 4 fields and 4 lines. 

ClearCollect( jsonlist , {field1:"hop0",field2:"ach0",field3:"bah0"},{field1:"hop1",field2:"ach1",field3:"bah1"},{field1:"hop2",field2:"ach2",field3:"bah2"},{field1:"hop3",field2:"ach3",field3:"bah3"} )

Putting the exact same JSON in a variable "jsntxt" and then giving it to ClearCollect,

ClearCollect( jsonlist ,  jsntxt );

 Creates a collection with one value that has the whole JSON text, basically refusing to interpret it.

Does anyone see a workaround to get Power Apps to do what I ask.

Thanks,

Michel

[Pstork1]

Unfortunately, I don't think that's possible in Power Apps. The problem is that when you do it directly the record designator {} is a control character.  When inside a variable its part of a string.   The context is completely different.  The only way to do what you are trying that I know of is using Power Automate's Parse JSON action.  You could pass the string to a Flow, have the flow parse it to a JSON Array and then pass it back.  But that would require a premium license to send it back.  

There is an open request for adding a Parse Json command to Power Apps.  You can find it here if you would like to vote it up.

Parse JSON in PowerApps - Power Platform Community (microsoft.com)

[MichelH]

Thanks for the quick response @Pstork1 ,

Does you answer imply that if I remove the { and } from the string, and then call the Collect with { txtvar }, where txtvar has my  field:"value" that it would accept it?

No, it looks like ClearCollect( jsonlist , { jsntxt } ); doesn't want to interpret the jsntxt variable.

But like this it will again interpret the variable ClearCollect( jsonlist , { field1:jsntxt } );

Looks like MS has put in a lot of effort to render this useless, pushing us into the license trap.

Thanks,

Michel

[Pstork1]

That won't work either.  The field names are part of the data structure also.  It has to be an actual data structure, not a string representing the data structure.  What you want is the opposite of what the JSON() function does.  There is currently no way to do that in Power Apps, only in Power Automate.  You can use strings as the values for the fields, but the field names and other pieces of the data structure can't be made dynamic.

I don't think it was a conscious attempt to force the use of Premium licensing in Power Automate.  That just happens to be a workaround that is functional since the ParseJson action is part of Logic Apps

[MichelH]

I see what you are saying @Pstork1,

But I can write something that parses the JSON, but it is made difficult to push the result into a collection. And there simply is no good reason for doing that.

I've gotten it to work pushing just the values via a ForAll into a collection. looping that, to recreate the original collection that I want to save and retrieve. 

I can do this because the list format is static, only the number of items will change.

I cannot care about about MS licensing. I have premium.

I'm dealing with business processes, so I want things to be as simple, straightforward and reliable, as possible.

That means I don't want to add a flow call, just because MS needs to make a profit.

I'm surprised to see that the power apps JSON function, produces a sort of JSON that power automate doesn't understand, because also the field names are quoted. 

That sort things makes me feel MS needs to get a grip on what they are doing.

[Pstork1]

Not trying to start an argument, but a couple corrections.

1) If you have a premium license then using a flow doesn't cost you anything and doesn't make any additional profit for Microsoft.  I frequently use Power Automate with Power Apps because Power Apps is a declarative language and Power Automate is procedural. But there are also things that Power Apps can do that Power Automate can't.  For example, Power Automate has no function for doing REGEX expressions.

2) I'm not sure I understand your comment about the Power Apps JSON function.  Power Automate does understand the JSON produced by JSON().  It is the way I frequently pass arrays of data to a flow and back to an App.

[MichelH]

No argument @Pstork1,

I'm perfectly OK with you or anyone having another opinion.  🙂

And you are right that I can add a flow to my app, without extra costs toward MS, because of the premium license. However I will always try to avoid doing that if I can, because I believe in keeping things simple. Adding unnecessary complexity to a solution, or spreading a solution over platforms, definitely adds a cost when it comes to training, maintenance and troubleshooting of that solution.

Thanks for your help.

Michel 

[Pstork1]

I understand the concern about double training.  In my view its using the right tool for the right task.  AS you've seen with your ForAll() workaround sometimes something that is simple in one tool is much more complex in another.

[VenkatK1]

Here is how to push JSON string into collection. I got  the idea from Reza Dorrani video on ParseJson.

https://www.youtube.com/watch?v=FqfLiJDdC3Q

Here is a sample JSON array as string which is set to variable accountItemsJson

[

{"AccountNo":"ab","Amount":"55","Description":"cd"},

{"AccountNo":"de","Amount":"552","Description":"ef"},

{"AccountNo":"h2","Amount":"3330","Description":"ddd"}

]

I have a collection colAccountItems

//Check and add account items to collection

If(accountItemsJson <> "",
//When data exists
ForAll(
Table(ParseJSON(accountItemsJson)),
Collect(
colAccountItems,
{
LineNumber: Value(ThisRecord.Value.LineNumber),
AccountNo: Text(ThisRecord.Value.AccountNo),
Description: Text(ThisRecord.Value.Description),
Amount: Value(ThisRecord.Value.Amount)
}
)

);
UpdateContext({varTotalAccountItems: CountRows(colAccountItems)});
,
//New
UpdateContext({varTotalAccountItems: 0});
)