How to parse JSON in PowerApps? OR How to get/set JSON data in SharePoint list column?

[BJoshi]

Hello Everyone,

I have stored data in JSON data under the SharePoint list "multiline and text" column. J needs to retrieve that JSON data on the PowerApp form and update that JSON data from the PowerApp form.

Can anyone suggest me for the same?

[Nogueira1306]

Check this 2 links:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Parse-JSON-string-in-Power-APPS/td-p/381832 

https://powerusers.microsoft.com/t5/Power-Apps-Portals/How-to-parse-JSON-in-PowerApps/td-p/783737 

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

[RandyHayes]

@BJoshi 

Yes, but there is not a function in PowerApps to convert to data.

Take a look at this post - it was based more on API results, but the point was, it was JSON.  In that in the second message, I have a formula there that will convert JSON to data.  It is far superior than trying to do substitute functions and other string handling.  The only changes you need to make are on the column names.

From that post, the following formula is found:

Set(APIResultRecord,
    With({_apiResult: nametest.RunAPI({rxid: TestInput1_1.Text, format: TextInput1_2.Text}).Substances.data)},
        With({convertedJSON:
            ForAll(MatchAll(_apiResult, """(?<nvalue>[^""]+)"": *""?(?<jvalue>[^(""|\n)]+)""?").SubMatches,
                {Name:First(SubMatches).Value, Value:Last(SubMatches).Value}
            )},

        {uuid: LookUp(convertedJSON, Name="uuid", Value), 
         nsinchikey: LookUp(convertedJSON, Name="nsinchikey", Value),
         inchikey : LookUp(convertedJSON, Name="inchikey", Value),
         smiles: LookUp(convertedJSON, Name="smiles", Value),
         role: LookUp(convertedJSON, Name="role", Value)
        }
    )
)

I use that similar formula often to restore JSON data that is in a Multiline SharePoint columns.  I put a lot of user preferences in JSON in multiline columns.  To restore them from the JSON, I use the above formula with a slight variation.  Here is an example:

With({prefRecord: 
    LookUp(Apps, Title=glbAppInfo.AppName && UserEmail=glbAppInfo.EmailLower, Prefs)},

       With({convertedJSON:
           ForAll(MatchAll(prefRecord, """(?<nvalue>[^""]+)"": *""?(?<jvalue>[^(""|,|\n)]+)""?").SubMatches As ms,
               {Name:First(ms.SubMatches).Value, Value:Last(ms.SubMatches).Value}
           )},

           {
             FontSize : Value(LookUp(convertedJSON, Name="FontSize", Value)),
             ThemeName: LookUp(convertedJSON, Name="ThemeName", Value),
             ShowImages: (LookUp(convertedJSON, Name="ShowImages", Value) = "true"),
             Sort: {Column: LookUp(convertedJSON, Name="SortColumn", Value),
                    Order: Switch(LookUp(convertedJSON, Name="SortOrder", Value), 
                           "ascending", SortOrder.Ascending, SortOrder.Descending)
                    },
             ShowWallpaper: (LookUp(convertedJSON, Name="ShowWallpaper", Value) = "true"),
             Language: LookUp(glbAppInfo.Languages, Language=Coalesce(LookUp(convertedJSON, Name="Language", Value), "English"))                     
           }
       )
)

 The above converts a JSON string that has values for a 1 numeric, 2 text, 2 boolean and 1 complex elements of the JSON string.

There is actually more to that formula that I removed for clarity (the formula actually evaluates theme values as well, but I didn't want to overcomplicate for you purposes of example).

I hope this is helpful for you.

[HEATFreight]

@RandyHayes thanks for this wonderful bit of code! Got it working but running into a bit of trouble with complex JSON, namely: nested objects, integer values, null values, and arrays of integers.

Symptoms:
• The value stored for any value not followed by double quotes or a comma will end with at least one ] or } depending on the relevant JSON structure.
• Null values become a string of text like "null", which would be fine if not for the first issue above

• To summarize the above two issues, values such as <null> or <0>, where the characters inside the carrots are the string value in question, get appended with the trailing brackets of the JSON if they happen to fall at the end of an array or object (where no comma or double quote will follow). So basically the regex doesn't break out until the next comma or double quote.

• Nested JSON objects with generic keys are hard to parse using this method. It seems that some preparation of the JSON format is necessary to make it work cleanly with regex in Power Apps. Have data from an API of the structure:

"ArrayData":[
{"key":"data","value":"more data"},
{"key":"data2","value":"more data2"},
{"key":"data3","value":"more data3"}
]

 where the keys "key" and "value" are always called "key" and "value". And that wouldn't be so bad, or "key" is easy enough to parse anyway, but "value" is tricky because it comes up so often in other places. So what I really want is to select the more specific "ArrayData", but am having trouble parsing entire objects and arrays using the regex MatchAll function in my Power Apps canvas app.

If I were to prep the data in Power Automate to remove as much of the nesting as possible, that would probably help a lot, but it seems that there should be some way to cleanly achieve what I want. I've got arrays of integers with no keys! I've got arrays of nested objects within objects within an outer array! I don't have the time or mental bandwidth to jump around between various arbitrary ways of structuring the same data. I just need Power Apps to interpret the JSON as any JSON interpreter would. Why can't Power Apps parse JSON out of the box?

Anyway, thanks again Randy! Any wisdom about dealing with complex JSON would be greatly appreciated.

[HEATFreight]

Aha! Got it working @RandyHayes !!!

Thanks for the excellent nested With-With-ForAll-MatchAll example! That was the magic thinking I needed...

I now have a nested MatchAll like

>With
     >ForAll
            >With
                  >With
                         >MatchAll
     >ForAll
            >With
                  >ForAll
                         >MatchAll
                  >ForAll
                         >Collect

I'm pulling entire complex JSON records from a single multiline text column in a SharePoint record. This is a total eureka moment for me, because I was struggling with this exact problem for about a year! Unfortunately, I had to prep the JSON to remove nested objects. I could not get a regex working on anything with nested objects but was able to de-nest those objects on a per-record basis in Power Automate before stringifying the JSON and writing to a single SharePoint column. I have an input JSON column (for inputting the same data structure fully nested and ready for Power Automate HTTP API request with minimal restructuring, for instance to fix the problem that arrays of keyless integers get converted to [{Value: 1},{Value: 2} ] instead of [1,2]...) and I would love for the data structure of the input (Power Apps -> Power Automate) column to match that of the output (Power Automate -> Power Apps) column, but that is so difficult to do! Why can't I have (what goes in) == (what goes out) ??? Power Apps is massively deficient in this regard. The JSON() function in Power Automate converts even escaped JSON strings into parametric JSON objects, but the JSON() function in Power Apps basically just formats objects into a JSON-formatted string. Basically, they are opposites. So how does one effortlessly parse a JSON string in Power Apps?

Well this With()-MatchAll() magic is about as close as I have come, but I won't call it "effortless"!

EDIT: I can probably simplify this by combining some of that With() logic, at least the two directly nested With() functions.

EDIT2: To extract the most complicated nested array from the JSON string, in Power Automate I put "OutputJSON":"@{string(outputs('JSON_String'))}" in a SharePoint HTTP request, and for the array value in that outputs('JSON_String') I used "array":"@{string(outputs('array'))}", thus the innermost complex array string was double stringified, meaning all double quotes preceded by backslash, like \". Here is the regex I used to obtain all the keys and values from that \-escaped JSON string:

        ForAll(_Data.array,
                With({_array_breakout:ForAll(ThisRecord.array.Value,MatchAll(Value,"\{?\\""(?<nvalue>[^""\\]+)\\"": *\\?""?(?<jvalue>[^,\\""|\}]+)\\?""?\}?"))},
                    //Collect(colTestBreakoutValue,_array_breakout); //test or debug line
                    ForAll(_array_breakout.Value,
                        Collect(col_array_breakout,
                        {
                        referenceNumbers: LookUp(Value, nvalue="referenceNumbers").jvalue
                        }); //collect
                    ); //ForAll   
                ) //With
        ) //ForAll

This specifically works only with JSON strings like:

{\"key1\":\"value\",\"key2\":123,\"key3\":[]}

And the other bit of magic was that, before stringifying that array in Power Automate, I prepared it by prepending and appending a buffer string to each stringified row. The resulting regex looks like this:

With({_Data:
        ForAll(colData,
            With({_colDataToBreakOut: ThisRecord.OutputJSON},
                With({_colArrayToBreakout: MatchAll(_colDataToBreakOut,"(\|\|\|\|\$\$\$\$\|\|\|\|)\{\\""(?<Value>[^(\|\|\|\|\$\$\$\$\|\|\|\|)]+)").Value},
                {array:_colArrayToBreakout}
                ) //With
            ) //With
        ) //ForAll
    }, //With

Putting all my code together:

With({_Data:
        ForAll(colData,
            With({_colDataToBreakOut: ThisRecord.OutputJSON},
                With({_colArrayToBreakout: MatchAll(_colDataToBreakOut,"(\|\|\|\|\$\$\$\$\|\|\|\|)\{\\""(?<Value>[^(\|\|\|\|\$\$\$\$\|\|\|\|)]+)").Value},
                {array:_colArrayToBreakout}
                ) //With
            ) //With
        ) //ForAll
    }, //With
        ForAll(_Data.array,
            With({_array_breakout:ForAll(ThisRecord.array.Value,MatchAll(Value,"\{?\\""(?<nvalue>[^""\\]+)\\"": *\\?""?(?<jvalue>[^,\\""|\}]+)\\?""?\}?"))},
                //Collect(colTestBreakoutValue,_array_breakout); //test or debug line
                ForAll(_array_breakout.Value,
                    Collect(col_array_breakout,
                    {
                    referenceNumbers: LookUp(Value, nvalue="referenceNumbers").jvalue
                    }); //collect
                ); //ForAll   
            ) //With
    ) //ForAll
); //With

The buffer string I used was:

||||$$$$||||

If you prepare the JSON string in Power Automate, you can extract a complex array in Power Apps using the above technique. I think I can further improve this if I can double stringify the entire JSON array (whereas my outer array of objects was not backslash-escaped like the above inner array example. So I'll need an entirely different regex to obtain that data. I was having difficulties with that before, but I believe the backslash-escaped double-quotes are actually easier to parse successfully! So I might try to simplify and improve on this method by backslash-escaping every double-quote. Anyway, here's a simple example of the JSON structure the above regex works with (whitespace added for readability):

[{"OuterKey1":"OuterValue1","OuterKey2":123,"OuterKey3":
     "||||$$$$||||{\"key1\":\"value\",\"key2\":456,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":789,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":101112,\"key3\":[]}||||$$$$||||"
},

{"OuterKey1":"OuterValue2","OuterKey2":069,"OuterKey3":
     "||||$$$$||||{\"key1\":\"value\",\"key2\":456,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":789,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":101112,\"key3\":[]}||||$$$$||||"
},

{"OuterKey1":"OuterValue3","OuterKey2":1337,"OuterKey3":
     "||||$$$$||||{\"key1\":\"value\",\"key2\":456,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":789,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":101112,\"key3\":[]}||||$$$$||||"
}]

So as I have it written in the code above, those OuterKeys and OuterValues would not be parsed. Only the backslash-escaped array "OuterKey3" gets parsed. Now I need to figure out the simplest way to get the rest of the record (the outer keys and values) broken out into the rows of the inner records such that each inner record also contains the outer record.

Previously, I dealt with that by just making as many outer records in SharePoint as necessary to account for all the inner records, but man that was a boondoggle in so many ways. I've got it figured out now where I can reliably get some JSON into Power Automate via a single SharePoint column (or Power Apps connector) to make an HTTP API request, but reversing that logic from Power Automate back to a single SharePoint column (and Power Apps as well via that single SharePoint column) has been the most challenging experience of my time in using the Power suite and powerFX language. Sending JSON back to Power Apps via the Response action in Power Automate is relatively easy. It's no simple task to do the same via SharePoint. I've been using MatchAll() for awhile, but I was limited to JSON with no nested arrays or object and only strings. This is the first time I've been able to find a working regex for the more complicated JSON structure. One of the big reasons is I keep forgetting that every double-quote in a regex tester must get escaped with another double-quote, like "" instead of ", in the MatchAll() regex string in Power Apps. I probably would have had this sorted out much faster if I'd realized that earlier on!

But I digress... Hopefully my struggle was helpful. Will update if I find a way to parse the whole JSON structure without any special modifications or complexities! Thanks again to Randy!

FINAL EDIT: I revised the logic above to be simpler and use as few With() statements as possible, because usually any two nested With() statements can be combined into one With() statement [ assuming the hierarchy is With({}, With({})) rather than, say, With({}, ForAll({}, With({}))) ].

So, now I have an initial With() statement which parses the outer array of objects, then collects those keys and values into rows. After that, a separate With() statement breaks out the padded rows of an array contained within a backslash-escaped JSON string which was stored in one of the parameters from the data we parsed in the first With() statement. And the second argument of that second With() statement breaks out the keys and values from each row of that inner array, then collects that data into rows joined with the data from the first With() statement. Thus, we have de-nested an array of JSON objects from within another array of JSON objects such that each row of the inner array is joined with the entire set of data from the object in which it was contained. So, the total number of records is the sum of all the rows from all the inner arrays. Each record from an inner array is now in a row with all the data from the corresponding outer record, and each inner record from the same inner array therefore has an exact copy of the that corresponding outer record.

The idea was that —although it's possible to de-nest the inner array directly into the SharePoint list such that the SP list would have as many records as the total number of rows in the inner arrays (just like the collection we created with this regex logic)— that seems to, at a minimum, double the data footprint in terms of number of records required in SharePoint, not to mention that dealing with records requiring more than one row of a SharePoint list is an insanely difficult boondoggle and probably a bit slower too. That was how we were previously handling this complex, nested JSON. But now we've got it where we can extract the entire complex JSON from a single multiline column in SharePoint. This massively simplifies so many contrivances required to implement the multiple-rows-per-record paradigm that we previously used. And prior to that, we used two different lists! We used to have one list for the outer object and a separate list for the inner array such that each record in the main list would have 2 or more corresponding records in the secondary list. Wow, we have come a long way since that time! Sure, we had to preemptively de-nest parts of the complex JSON and stringify the inner array, but we're extracting that data on the fly from a single cell per record! What a time to be alive!

Code:

Clear(colBreakout);
//
With({_data:
    ForAll(colData,MatchAll(ThisRecord.OutputJSON,"""(?<nvalue>[^(\\"")]+)"":""?(?<jvalue>[^""|]+)""?,"))},
    ForAll(_data.Value As dataBreakout,Collect(colBreakout,{
        Title: LookUp(dataBreakout.Value, nvalue="Title").jvalue,
        ID: LookUp(dataBreakout.Value, nvalue="ID").jvalue
        }) //Collect
    )// ForAll
); //With
//
//
Clear(colAdditionalData);
//
//
    With({_dataArray:
        ForAll(colData,
                {_array: MatchAll(ThisRecord.OutputJSON,"(\|\|\|\|\$\$\$\$\|\|\|\|)\{\\""(?<dataValue>[^(\|\|\|\|\$\$\$\$\|\|\|\|)]+)").dataValue}
        ) //ForAll
    }, //With
        ForAll(_dataArray._array,
                With({_arrayBreakout:ForAll(ThisRecord._array.dataValue,MatchAll(dataValue,"\{?\\""(?<nvalue>[^""\\]+)\\"": *\\?""?(?<jvalue>[^,\\""|\}]+)\\?""?\}?"))},
                    ForAll(_arrayBreakout.Value,
                        With({_recordToPatch:
                        {
                        Title: LookUp(colBreakout, ID=LookUp(Value, nvalue="RecordID").jvalue).Title,
                        RecordID: LookUp(Value, nvalue="RecordID").jvalue,
                        sequence: Value(LookUp(Value, nvalue="sequence").jvalue),
                        referenceNumbers: LookUp(Value, nvalue="referenceNumbers").jvalue
                        }},
                        If(Value(LookUp(Value, nvalue="sequence").jvalue)=1,Patch(colData,{ID:LookUp(Value, nvalue="RecordID").jvalue},_recordToPatch),{_AdditionalData: Collect(colAdditionalData,_recordToPatch)})); //collect or patch
                    ); //ForAll
                ) //With
        ) //ForAll
    ); //With
//

The buffer string I used was:

||||$$$$||||

Example of the JSON structure the above regex works with (whitespace added for readability):

[{"OuterKey1":"OuterValue1","OuterKey2":123,"OuterKey3":
     "||||$$$$||||{\"key1\":\"value\",\"key2\":456,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":789,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":101112,\"key3\":[]}||||$$$$||||"
},

{"OuterKey1":"OuterValue2","OuterKey2":069,"OuterKey3":
     "||||$$$$||||{\"key1\":\"value\",\"key2\":456,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":789,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":101112,\"key3\":[]}||||$$$$||||"
},

{"OuterKey1":"OuterValue3","OuterKey2":1337,"OuterKey3":
     "||||$$$$||||{\"key1\":\"value\",\"key2\":456,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":789,\"key3\":[]}||||$$$$||||
     ||||$$$$||||{\"key1\":\"value\",\"key2\":101112,\"key3\":[]}||||$$$$||||"
}]