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"
}
}
}
}
}
- How to return JSON objects without explicit aliasing in postgresql
- jq: assigning to computed property of an object
- is form enctype "application/json" available?
- How do I deserialize JSON in Rust when combining a flattened component with the need to convert keys?
- How to sort order of stacked bar
- How can I parse (read) and use JSON in Python?
- How to recursively print the path of all keys in jq
- Accessing a Variant/Object/JScriptTypeInfo property in VBA
- Using jq to group-concat fields by field name prefix
- How to parse json in flutter Dart
- how to convert json string to json object in dart flutter?
- How Best to Extract Specific JSON Key-Value Pairs in Snowflake
- laravel crashed when I use trait to auto update some values
- Object Serialization to JSON (using Gson). How to set field names in UpperCamelCase?
- How to parse JSON response in filemaker script
- Importing JSON file in TypeScript
- Formatting parameters for C# HttpClient.PostAsJsonAsync
- Swift - Wrong date format (JSON .NET)
- JSON vs JSON Array in .NET Web API - Bandwidth V2
- Jolt transform JSON to different JSON structure
- Is there a way in PHP to strip whitespace from JSON without using ob_gzhandler?
- How can I separate a .json file by keys stored in a different .json file?
- What is the difference between using express.urlencoded() with extended set to true vs set to false + manual JSON stringify/parse calls?
- How to globally set default options for System.Text.Json.JsonSerializer?
- Can I encode and decode a JSON on the GPU using MetalKit?
- Java - Magento2 rest api product upload with image silently fails
- Filtering mysql data using nested json_table returns "Incorrect arguments"
- Can I turn a Pandas Dataframe column containing a jJSON string into additional columns and rows?
- Convert an array of unsigned short to JSON using the nlohmann library
- Redshift COPY with JSON PATH Case Sensitivity