jsonloopsazure-logic-apps
Parsing JSON Array of Strings in Logic Apps
I have been asked to report ADF pipeline status via email from Logic Apps. I have a table containing ClientFeedID, ClientName, PipelineName, RunStatus, RunStart and RunEnd columns which I pass as JSON from ADF to Logic Apps in an attempt to generate an email summarizing status. Input JSON as passed from ADF:
"runPageUrl": "https://myresource.azuredatabricks.net",
"runOutput": [
"{\"ClientFeedID\":\"31\",\"ClientName\":\"Client1\",\"PipelineName\":\"parent-client1\",\"RunStatus\":\"InProgress\",\"RunStart\":\"2024-08-07T12:00:00.9008853Z\",\"RunEnd\":\"NULL\"}",
"{\"ClientFeedID\":\"119\",\"ClientName\":\"Client2\",\"PipelineName\":\"parent-client2\",\"RunStatus\":\"InProgress\",\"RunStart\":\"2024-08-07T12:39:14.6654259Z\",\"RunEnd\":\"NULL\"}",
"{\"ClientFeedID\":\"19\",\"ClientName\":\"Client3\",\"PipelineName\":\"parent-client3\",\"RunStatus\":\"Succeeded\",\"RunStart\":\"2024-08-07T03:48:09.2485603Z\",\"RunEnd\":\"2024-08-07T04:17:51.4093277Z\"}",
"{\"ClientFeedID\":\"14\",\"ClientName\":\"Client4\",\"PipelineName\":\"parent-client4\",\"RunStatus\":\"Failed\",\"RunStart\":\"2024-08-07T13:45:00.090202Z\",\"RunEnd\":\"2024-08-07T11:22:05.9396317Z\"}"
],
"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US)",
"executionDuration": 84,
"durationInQueue": {
"integrationRuntimeQueue": 0
},
"billingReference": {
"activityType": "ExternalActivity",
"billableDuration": [
{
"meterType": "AzureIR",
"duration": 0.03333333333333333,
"unit": "Hours"
}
]
}
}
runOutput appears to be an array of strings and I need some help determining how to separate the individual columns in each element of the array.
I have made several attempts in Logic Apps using the answers from many questions on SO. My current Logic App appears as follows:
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"contentVersion": "1.0.0.0",
"triggers": {
"manual": {
"type": "Request",
"kind": "Http",
"inputs": {
"method": "POST",
"schema": {
"type": "object",
"properties": {
"runPageUrl": {
"type": "string"
},
"runOutput": {
"type": "array",
"items": {
"type": "string"
}
},
"effectiveIntegrationRuntime": {
"type": "string"
},
"executionDuration": {
"type": "integer"
},
"durationInQueue": {
"type": "object",
"properties": {
"integrationRuntimeQueue": {
"type": "integer"
}
}
},
"billingReference": {
"type": "object",
"properties": {
"activityType": {
"type": "string"
},
"billableDuration": {
"type": "array",
"items": {
"type": "object",
"properties": {
"meterType": {
"type": "string"
},
"duration": {
"type": "number"
},
"unit": {
"type": "string"
}
},
"required": [
"meterType",
"duration",
"unit"
]
}
}
}
}
}
}
}
}
},
"actions": {
"Send_an_email_(V2)": {
"type": "ApiConnection",
"inputs": {
"host": {
"connection": {
"name": "@parameters('$connections')['office365-1']['connectionId']"
}
},
"method": "post",
"body": {
"To": "Just.Bob@myemail.com",
"Subject": "Pipeline Summary",
"Body": "<p class=\"editor-paragraph\">@{body('Create_HTML_table')}</p>",
"Importance": "Normal"
},
"path": "/v2/Mail"
},
"runAfter": {
"For_each": [
"Succeeded"
]
}
},
"Initialize_ClientFeedID": {
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "ClientFeedID",
"type": "string"
}
]
},
"runAfter": {}
},
"Initialize_ClientName": {
"type": "InitializeVariable",
"inputs": {
"variables": [
{
"name": "ClientName",
"type": "string"
}
]
},
"runAfter": {
"Initialize_ClientFeedID": [
"Succeeded"
]
}
},
"For_each": {
"type": "Foreach",
"foreach": "@body('Parse_JSON')",
"actions": {
"Create_HTML_table": {
"type": "Table",
"inputs": {
"from": "@body('Parse_JSON')",
"format": "HTML",
"columns": [
{
"header": "ClientFeedID",
"value": "@variables('ClientFeedID')"
}
]
},
"runAfter": {
"Set_ClientFeedID": [
"Succeeded"
]
}
},
"Set_ClientFeedID": {
"type": "SetVariable",
"inputs": {
"name": "ClientFeedID",
"value": "@items('For_each').ClientFeedID"
}
}
},
"runAfter": {
"Parse_JSON": [
"Succeeded"
]
},
"runtimeConfiguration": {
"concurrency": {
"repetitions": 1
}
}
},
"Parse_JSON": {
"type": "ParseJson",
"inputs": {
"content": "@outputs('Compose')",
"schema": {
"properties": {
"ClientFeedID": {
"type": "string"
},
"ClientName": {
"type": "string"
},
"PipelineName": {
"type": "string"
},
"RunStatus": {
"type": "string"
},
"RunStart": {
"type": "string"
},
"RunEnd": {
"type": "string"
}
}
}
},
"runAfter": {
"Compose": [
"Succeeded"
]
}
},
"Compose": {
"type": "Compose",
"inputs": "@triggerBody()?['runOutput']",
"runAfter": {
"Initialize_ClientName": [
"Succeeded"
]
}
}
},
"outputs": {},
"parameters": {
"$connections": {
"type": "Object",
"defaultValue": {}
}
}
},
"parameters": {
"$connections": {
"value": {
"office365-1": {
"id": "/subscriptions/mysubscriptions",
"connectionId": "/subscriptions/myconnections/office365-4",
"connectionName": "office365-4"
}
}
}
}
}
Output from Compose:
"{\"ClientFeedID\":\"31\",\"ClientName\":\"Client1\",\"PipelineName\":\"parent-client1\",\"RunStatus\":\"InProgress\",\"RunStart\":\"2024-08-07T12:00:00.9008853Z\",\"RunEnd\":\"NULL\"}",
"{\"ClientFeedID\":\"119\",\"ClientName\":\"Client2\",\"PipelineName\":\"parent-client2\",\"RunStatus\":\"InProgress\",\"RunStart\":\"2024-08-07T12:39:14.6654259Z\",\"RunEnd\":\"NULL\"}",
"{\"ClientFeedID\":\"19\",\"ClientName\":\"Client3\",\"PipelineName\":\"parent-client3\",\"RunStatus\":\"Succeeded\",\"RunStart\":\"2024-08-07T03:48:09.2485603Z\",\"RunEnd\":\"2024-08-07T04:17:51.4093277Z\"}",
"{\"ClientFeedID\":\"14\",\"ClientName\":\"Client4\",\"PipelineName\":\"parent-client4\",\"RunStatus\":\"Failed\",\"RunStart\":\"2024-08-07T13:45:00.090202Z\",\"RunEnd\":\"2024-08-07T11:22:05.9396317Z\"}"
]
Identical to Input
Schema for Parse JSON:
"properties": {
"ClientFeedID": {
"type": "string"
},
"ClientName": {
"type": "string"
},
"PipelineName": {
"type": "string"
},
"RunStatus": {
"type": "string"
},
"RunStart": {
"type": "string"
},
"RunEnd": {
"type": "string"
}
}
}
Output from Parse JSON:
"{\"ClientFeedID\":\"31\",\"ClientName\":\"Client1\",\"PipelineName\":\"parent-client1\",\"RunStatus\":\"InProgress\",\"RunStart\":\"2024-08-07T12:00:00.9008853Z\",\"RunEnd\":\"NULL\"}",
"{\"ClientFeedID\":\"119\",\"ClientName\":\"Client2\",\"PipelineName\":\"parent-client2\",\"RunStatus\":\"InProgress\",\"RunStart\":\"2024-08-07T12:39:14.6654259Z\",\"RunEnd\":\"NULL\"}",
"{\"ClientFeedID\":\"19\",\"ClientName\":\"Client3\",\"PipelineName\":\"parent-client3\",\"RunStatus\":\"Succeeded\",\"RunStart\":\"2024-08-07T03:48:09.2485603Z\",\"RunEnd\":\"2024-08-07T04:17:51.4093277Z\"}",
"{\"ClientFeedID\":\"14\",\"ClientName\":\"Client4\",\"PipelineName\":\"parent-client4\",\"RunStatus\":\"Failed\",\"RunStart\":\"2024-08-07T13:45:00.090202Z\",\"RunEnd\":\"2024-08-07T11:22:05.9396317Z\"}"
]
Set ClientFeedID in For each returns:
InvalidTemplate Unable to process template language expressions in action 'Set_ClientFeedID' inputs at line '0' and column '0': 'The template language expression 'items('For_each').ClientFeedID' cannot be evaluated because property 'ClientFeedID' cannot be selected. Property selection is not supported on values of type 'String'. Please see https://aka.ms/logicexpressions for usage details.'.
My expectation was that I could grab the set of values from each array element and use them as row values within the email. As a newbie to Logic Apps, I am clearly lost in the weeds and have spent an embarrassing amount of time trying to get this running.
Thank You in advance for any and all help!
Solution
I have used the given workflow and it worked for me.
Set Concurrency control to 1 in For-Each loop.
Using below schema in Parse_JSON action.
{
"type": "object",
"properties": {
"type": {
"type": "string"
},
"properties": {
"type": "object",
"properties": {
"ClientFeedID": {
"type": "object",
"properties": {
"type": {
"type": "string"
}
}
},
"ClientName": {
"type": "object",
"properties": {
"type": {
"type": "string"
}
}
},
"PipelineName": {
"type": "object",
"properties": {
"type": {
"type": "string"
}
}
},
"RunStatus": {
"type": "object",
"properties": {
"type": {
"type": "string"
}
}
},
"RunStart": {
"type": "object",
"properties": {
"type": {
"type": "string"
}
}
},
"RunEnd": {
"type": "object",
"properties": {
"type": {
"type": "string"
}
}
}
}
}
}
}
Code-
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"Append_to_string_variable_1": {
"inputs": {
"name": "Email Table",
"value": "</table>"
},
"runAfter": {
"For_each": [
"Succeeded"
]
},
"type": "AppendToStringVariable"
},
"For_each": {
"actions": {
"Append_to_string_variable": {
"inputs": {
"name": "Email Table",
"value": "<tr>\n <td>@{body('Parse_JSON')?['ClientFeedID']}</td>\n <td>@{body('Parse_JSON')?['ClientName']}</td>\n <td>@{body('Parse_JSON')?['PipelineName']}</td>\n <td>@{body('Parse_JSON')?['RunStatus']}</td>\n <td>@{body('Parse_JSON')?['RunStart']}</td>\n <td>@{body('Parse_JSON')?['RunEnd']}</td>\n</tr>\n"
},
"runAfter": {
"Parse_JSON": [
"Succeeded"
]
},
"type": "AppendToStringVariable"
},
"Parse_JSON": {
"inputs": {
"content": "@items('For_each')",
"schema": {
"properties": {
"properties": {
"properties": {
"ClientFeedID": {
"properties": {
"type": {
"type": "string"
}
},
"type": "object"
},
"ClientName": {
"properties": {
"type": {
"type": "string"
}
},
"type": "object"
},
"PipelineName": {
"properties": {
"type": {
"type": "string"
}
},
"type": "object"
},
"RunEnd": {
"properties": {
"type": {
"type": "string"
}
},
"type": "object"
},
"RunStart": {
"properties": {
"type": {
"type": "string"
}
},
"type": "object"
},
"RunStatus": {
"properties": {
"type": {
"type": "string"
}
},
"type": "object"
}
},
"type": "object"
},
"type": {
"type": "string"
}
},
"type": "object"
}
},
"type": "ParseJson"
}
},
"foreach": "@triggerBody()?['runOutput']",
"runAfter": {
"Set_variable": [
"Succeeded"
]
},
"runtimeConfiguration": {
"concurrency": {
"repetitions": 1
}
},
"type": "Foreach"
},
"Initialize_variable": {
"inputs": {
"variables": [
{
"name": "Email Table",
"type": "string"
}
]
},
"runAfter": {},
"type": "InitializeVariable"
},
"Send_an_email_(V2)": {
"inputs": {
"body": {
"Body": "<p class=\"editor-paragraph\">@{variables('Email Table')}</p>",
"Importance": "Normal",
"Subject": "Test Report",
"To": "********"
},
"host": {
"connection": {
"name": "@parameters('$connections')['office365']['connectionId']"
}
},
"method": "post",
"path": "/v2/Mail"
},
"runAfter": {
"Append_to_string_variable_1": [
"Succeeded"
]
},
"type": "ApiConnection"
},
"Set_variable": {
"inputs": {
"name": "Email Table",
"value": "<table border='1'><tr><th>ClientFeedID</th><th>ClientName</th><th>PipelineName</th><th>RunStatus</th><th>RunStart</th><th>RunEnd</th></tr>"
},
"runAfter": {
"Initialize_variable": [
"Succeeded"
]
},
"type": "SetVariable"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"parameters": {
"$connections": {
"defaultValue": {},
"type": "Object"
}
},
"triggers": {
"When_a_HTTP_request_is_received": {
"kind": "Http",
"type": "Request"
}
}
},
"parameters": {
"$connections": {
"value": {
"office365": {
"connectionId": "/subscriptions/0e******e7c/resourceGroups/****/providers/Microsoft.Web/connections/office365",
"connectionName": "office365",
"id": "/subscriptions/0e8********e7c/providers/Microsoft.Web/locations/eastus/managedApis/office365"
}
}
}
}
}
- Keep numbers which appear in both columns, in J lang
- Differentiation in J
- How to reshape an array with an arbitrary size in one dimension?
- Why is Insert (fold) right associative
- Write 4 : 'x&{.&.;: y' tacitly
- Alignment issue when printing formatted prime numbers in J language
- How can I define a verb in J that applies a different verb alternately to each atom in a list?
- How to get user input in the J programming language
- How to unbox a list of boxed lists of differing lengths in J?
- How can I fix 'noun result was required' error in J?
- In j, how can I define a verb locally in one scope and pass it to a defined adverb?
- Convert boxed array to normal array?
- Read column of CSV file as array
- Replace atom in array of strings
- What does the dyad `=` do to boxed strings?
- Index of minimum element using J
- How can I take the outer product of string vectors in J?
- Building an array of verbs in J
- Reading in multidigit command line parameter
- Amend with bond to new data shows unexpected behaviour
- How to turn a table or matrix into a (flat) list in J
- How to run dissect in J?
- How to define selection using index function in J
- How to exit the J console?
- Find 4-neighbors using J
- Writing custom verbs in J
- How do I negate a selector in J lang?
- How to use arbitrary selector in interchange in J lang?
- different result once square root is added inside tacit
- Sum of arrays with repeated indices