Parse string of json records and present as a table

[seacc]

Hi all, I'm fairly new to PowerApps and Flow and trying to get my head around the terminology.

I'm working with a database table that logs results from a set of data processing jobs. Each row in the table holds a blob string of correctly formatted json representing multiple rows from a specific data processing job. The json string holds a set of key : value pairs repeated multiple times to reflect multiple results for a given job.  The keys are different job to job. 

I'm trying to figure out whether there is a way to parse the json string and present the results in a table when a PowerApps user selects to see the results for a particular data processing job. The keys and thus the columns to be rendered if converting the json string to a table will differ from job to job. The json string for two jobs could be...

a row where the string contains two json results
'
{"RecordID":77880956,"Entity":"WEST","Currency":"USD","CALC_TotalAmount":3.20},{"RecordID":45632111,"Entity":"EAST","Currency":"USD","CALC_TotalAmount":34.45}
'

followed by a row where the string contains three json results and the keys are different from the previous row 

'

{"LINK":"88312106","DataSource":"EXTERNAL","AsOfDate":"2020-11-30T00:00:00","Region":"EAST"},

{"LINK":"34567890","DataSource":"INTERNAL","AsOfDate":"2020-11-30T00:00:00","Region":"CENTRAL"},

{"LINK":"56734512","DataSource":"EXTERNAL","AsOfDate":"2020-11-30T00:00:00","Region":"EAST"}

'

Thanks in advance for any guidance. 

[RandyHayes]

@seacc 

There is no function in PowerApps that lets you turn a JSON string into a record.

However, like many things, just because it doesn't exist doesn't mean you can't do it.  I will say though, this is going to be a challenge for someone new to PowerApps.

I can assist you on this, but I need to understand more about what you mean by "a row that contains two results" and "a row that contains three different results".  This is not typical for JSON to have two record types in the same definition. So I am wondering if there is something else in between the first two records and the last 3 records.

[seacc]

Hi @RandyHayes, thank you for the offer to assist! I'll try to explain more clearly.

The SQL Server table I'm connecting to has two columns
ReportName
Results

The contents of the 'Results' column is a set of multiple json records stored as a string. And the structure of the json records differs from Report to Report. Using the example json strings from my initial post, two rows from the table I'm connecting to via PowerApps would looke like...

ReportName	Results
report1	' {"RecordID":77880956,"Entity":"WEST","Currency":"USD","CALC_TotalAmount":3.20},{"RecordID":45632111,"Entity":"EAST","Currency":"USD","CALC_TotalAmount":34.45} '
report2	' {"LINK":"88312106","DataSource":"EXTERNAL","AsOfDate":"2020-11-30T00:00:00","Region":"EAST"}, {"LINK":"34567890","DataSource":"INTERNAL","AsOfDate":"2020-11-30T00:00:00","Region":"CENTRAL"}, {"LINK":"56734512","DataSource":"EXTERNAL","AsOfDate":"2020-11-30T00:00:00","Region":"EAST"} '

And the desired experience for a user is to select a given report (via 'ReportName') and then see a table with the contents of the Results column rendered as a table. Where the keys in the json string (and thus the required columns to render as a table) will differ depending on the report selected. 

I hope that more clearly explains the complexity of the question... Thank you for taking a look!