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\":[]}||||$$$$||||"
}]
