Access JSON data further in with SELECT

(0) Share

Report

Posted on by Emil2

Hello Community.

Hope this have not been asked because i could not find anything about it.

My situation

1. I read data from SQL with a query

2. I parse the data with a JSON

3. I initialise a variable since else i got error about type

4. I append to a array

5. I use a select to take out certain data from the array

Here is the data in the array:

{
 "ResultSets": {
 "Table1": [
 {
 "EwonName": "47",
 "TidPunkt": "2020-11-17T18:18:09Z",
 "Name": "TankConductivity",
 "Value": "0.381624698638916"
 },
 {
 "EwonName": "12",
 "TidPunkt": "2020-11-17T21:05:08Z",
 "Name": "TotalPermeateVolume",
 "Value": "11600"
 },
 {
 "EwonName": "44",
 "TidPunkt": "2020-11-17T12:34:44Z",
 "Name": "TotalPermeateVolume",
 "Value": "36730"
 }
 ]
 },
 "OutputParameters": {}
}

I can read out all the data with the select from the array with this function

first(variables('test1'))['ResultSets']

but then it gets messy in my send email funktion.

{"Table1":[{"EwonName":"33","TidPunkt":"2020-11-17T18:18:09Z","Name":"TankConductivity","Value":"0.381624698638916"},{"EwonName":"21","TidPunkt":"2020-11-18T05:05:02Z","Name":"TankConductivity","Value":"1.1649608612060547"},{"EwonName":"21","TidPunkt":"2020-11-18T04:30:22Z","Name":"TankConductivity","Value":"0.6224169731140137"},{"EwonName":"21","TidPunkt":"2020-11-17T22:41:21Z","Name":"TankConductivity","Value":"1.3767967224121094"},{"EwonName":"21","TidPunkt":"2020-11-17T05:41:04Z","Name":"TankConductivity","Value":"0.4563007354736328"},{"EwonName":"76","TidPunkt":"2020-11-17T21:04:08Z","Name":"TankConductivity","Value":"0.49059104919433594"},{"EwonName":"88","TidPunkt":"2020-11-17T17:11:53Z","Name":"TankConductivity","Value":"15.792694091796875"},{"EwonName":"prototype","TidPunkt":"2020-11-17T12:34:44Z","Name":"TankConductivity","Value":"0.47496986389160156"},{"EwonName":"1121","TidPunkt":"2020-11-17T12:29:49Z","Name":"TankConductivity","Value":"1.8328537940979004"},{"EwonName":"0606","TidPunkt":"2020-11-18T03:03:12Z","Name":"TankConductivity","Value":"0.46506404876708984"},{"EwonName":"1","TidPunkt":"2020-11-17T21:05:08Z","Name":"TankConductivity","Value":"0.534024715423584"},{"EwonName":"6","TidPunkt":"2020-11-

I want it to be listed as this ( just an example )

I found a tutorial where the author listed the variables like this in his select and then he could call them very nicely in the send email function

But he do not call the data from a SQL so i cant do it exact the same way as him.

So how would i do the same but from a JSON array, If i want to read out the EwonName etc?

Or any other smart way?

Here is the full flow

All responses (4)

Answers (1)

Emil2 21 on at

Like (0)

Report

Re: Access JSON data further in with SELECT

That made it.

Thank you very much

/Emil2
Verified answer

v-siky-msft on at

Like (0)

Report
Re: Access JSON data further in with SELECT

Hi @Emil2 ,

Notice 'Table1' is an Array, instead of an Object, please try to put the following sample data of Table1 to sample payload to generate the JSON schema.

[ { "EwonName": "47", "TidPunkt": "2020-11-17T18:18:09Z", "Name": "TankConductivity", "Value": "0.381624698638916" }, { "EwonName": "12", "TidPunkt": "2020-11-17T21:05:08Z", "Name": "TotalPermeateVolume", "Value": "11600" }, { "EwonName": "44", "TidPunkt": "2020-11-17T12:34:44Z", "Name": "TotalPermeateVolume", "Value": "36730" } ]

With regard to flow steps, only need to replace the 'Select' action by 'Parson JSON' action, and then use the parsed Body to create HTML Table.

Hope this helps.

Sik
Emil2 21 on at

Like (0)

Report

Re: Access JSON data further in with SELECT

Thank you very much, However i dont think i get the schema correct?

How do your schema looks?
Or do i missunderstand your flow, should i add a new parse JSON after the select and before the create html table
or should i remove everything before my Parse JSON and Create HTML?

All the best
/Emil2
v-siky-msft on at

Like (0)

Report
Re: Access JSON data further in with SELECT

Hi @Emil2 ,

Please first to use the following expression to output the Table1 Array, and use Parse JSON step to parse it, then use Body dynamic content of Parson JSON to create HTML table.

first(variables('test1'))['ResultSets']['Table1']

Test outcome:

Hope this helps.

Sik