question

Robin-3754 avatar image
0 Votes"
Robin-3754 asked MayankBargali-MSFT edited

How to iterate over stored procedure resultset to generate a JSON in Azure Logic Apps>?

Hi, I am getting below resultset from stored procedure in logic apps from Execute stored procedure V2. I want to transform it to JSON format.


{
"ResultSets": {
"Table1": [
{
"Number": "546546547",
"Fname": "Mick",
"Sname": "Tait",
"Test": "Only"
}
],
"Table2": [
{
"PID": 9797879,
"Randomdate": " 2020/1989",
"Random2": "TTTTT",
"Random": "Y7686786",
"EngineName": "V3",
"Vehicle": "Car",
"Emisssion": "BS4",
"Group": "test",
"Route": "ZigZag",
},
{
"PID": 6576556,
"Randomdate": " 2020/1988",
"Random2": "YYYYY",
"Random": "Z343544",
"EngineName": "V4",
"Vehicle": "Truck",
"Emisssion": "BS3",
"Group": "test2",
"Route": "Straight",
},
{
"PID": 465465465,
"Randomdate": " 2020/1987",
"Random2": "ZZZZZZ",
"Random": "R75747",
"EngineName": "V1",
"Vehicle": "SUV",
"Emisssion": "BS2",
"Group": "test3",
"Route": "Straight",
}
]
},
"ReturnCode": 0,
"OutputParameters": {}
}



Below is the Desired JSON format:


{
"NumberDetails": {
"Number": "string",
"Fname": "string",
"Sname": "string",
"FullName": "string",
"RandomDetails": {
"Random2": "string",
"Random": false,
"Products": {
"PID": "string",
"EngineName": "string",
"Vehicle": 0,
"Emisssion": 0,
"Group": "string",
"Route": "string"
}
}
}
}

azure-logic-apps
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MayankBargali-MSFT avatar image
0 Votes"
MayankBargali-MSFT answered MayankBargali-MSFT edited

@Robin-3754 You either need to write the inline code for your requirement or offload your functionality to function app. You can also loop in through your result table response and use variable to store and manage value as per your requirement but this might cost you more time depending upon your json result and actions used in your workflow for your transformation.

The Parse JSON action can access properties in JavaScript Object Notation (JSON) content that creates user-friendly fields or tokens for those properties and not for JSON grouping but you can use Parse JSON action for tokens and using the same in your for loop for the Compose action to generate your desire output.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.