How to make ParseJSON() work with proper JSON formatted table

[martinav]

Well, its no surprise.  They give us a potentially useful function like ParseJSON(), but the execution is convoluted.  The example given in the documentation show putting the JSON string into quotes, and double-quoting text devices within the string.  Of course, there is no straight forward way to do this.... is there?

https://learn.microsoft.com/en-us/power-platform/power-fx/reference/function-parsejson

The JSON I want to convert to a table is simple, and in a very tidy JSON file shown here:

[
  {
    "WorkOrderOB": "7307-18",
    "Action": "Updated"
  },
  {
    "WorkOrderOB": "4305-1",
    "Action": "Created"
  }
]

I dont like the idea of using Substitute() because you cannot be sure that a quote is not in the string.  I thought this was going to be easy, once I got the JSON string from Flow into PA.  Well, now there is another convoluted issue to overcome.

The example given is this:

ForAll( Table( ParseJSON( "[ { ""id"": 1, ""name"": ""one"" }, { ""id"": 2, ""name"": ""two"" } ]" ) ), { id: Value(ThisRecord.Value.id), name: Text(ThisRecord.Value.name) } )

This is the format that makes this statement work.  But, how do I reliably get this format?

ForAll( Table( ParseJSON( "[
  {
    ""WorkOrderOB"": ""7307-18"",
    ""Action"": ""Updated""
  },
  {
    ""WorkOrderOB"": ""4305-1"",
    ""Action"": ""Created""
  }
]" ) ), { WorkOrderOB: Text(ThisRecord.Value.WorkOrderOB), Action: Text(ThisRecord.Value.Action) } )

[martinav]

I really dont want to use a substitude() do this.  Funny, chatGPT gave a genius answer...

Text(JSONString, JSONFormat.IncludeQuotes)

However, there is no .IncludeQuotes statement in PA.  What a tease.

[cwebb365]

Shouldn't need to do all that, should be able to just say 

[cwebb365]

Didn't mean to hit save lol. Anyway, should be able to use this. A forAll alone creates a table. 

ForAll(ParseJSON(varJSON),{WorkOrderOB:ThisRecord.WorkOrderOB,Action:ThisRecord.Action})

varJSON can be replaced with a varable set to, or you can use the string form it direct in the forall. 

Set(varJSON,"[

  {

    ""WorkOrderOB"": ""7307-18"",

    ""Action"": ""Updated""

  },

  {

    ""WorkOrderOB"": ""4305-1"",

    ""Action"": ""Created""

  }

]")

[martinav]

@cwebb365 

The issue is not with the syntax of the ForAll, but the format of varJSON.  JSON output from MS Flow is single quote, but PA wants double quotes.  The question is how to convert the JSON format, either in Flow or PA.  I find no way without being complicated.  Unfortunately, I havent found how to do that yet.  Even submitting to using substitute, I havent found a working way yet.

ForAll(Table(
   ParseJSON(Concatenate("""",Substitute(UploadAction.actions,"""",""""""),""""))), 
            {WorkOrderOB:Text(ThisRecord.Value.WorkOrderOB),                                                                                                   
             Action:Text(ThisRecord.Value.Action)})

While this provides the proper double quote JSON, it gives this error:

Unfortunately, I spend more time on BS problems like this than actually creating something productive.

[cwebb365]

Hmm, when it comes over from flow, it should read it as a string/text object and the quotes shouldn't matter? You only need double quotes when trying to set/declare text to a variable / object from within PowerApps.

I just tested a flow using the raw Json passing output into variable and using it in the same ForAll(ParseJSON) and the gallery showed the Items fine. 

[martinav]

@cwebb365 

I dont know if i'm being clear.  The whole purpose that I am aware of with using ParseJSON is to do that very thing.  That is what I am needing to accomplish.  I need this JSON file to generate a table which I can then use the data as feedback for my app.   

[cwebb365]

I guess I don't understand, because I've taken your original json, put it in a compose action in flow with single quotes (the raw json) that output to powerapps. Then user set() to pass the flow output into a variable and used the variable in ParseJSON to get to the data using the ForAll example which is a table that can be used in Filters / Collections / Gallery etc. 

If that's not what you're trying to do then I guess I might not be understanding :). 

[martinav]

@cwebb365 

Im not sure what to say... because...

This works:

(Statement is in 'items' in a table object)

This does not:

I cannot repeat what your saying is true.  So, I dont know how to proceed.  The only difference in the two are single vs double quotes.  Until I can see it work this way, i wont even mess with Response.  One step at a time.

[cwebb365 · Accepted solution]

Yeah, if you are setting straight JSON with "Code" you have to double quote it. If you pass it in, from say Flow as a string object into powerapps, it doesn't have to be double quoted. The quotes are only for the code section. I guess the question is, where is the json coming from, if it's flow you don't have to worry about it, it should just part it right over when you pass in the flow run is what I'm saying. Or if you pull the json from a data source, it'll parsejson just fine. The only reason you have to deal with double quotes is manually setting variables/setting text input with the code editor.