CODEWITHSUNDEEP
azure-logic-apps
ZZZSharePoint
ZZZSharePoint

Reputation: 1341

How to check json and do filtering in Logic App

In my Logic App one of the action item gives me Json value as like this . This is recorded in compose action.

{
  "device1": 24,
  "device2": 25,
  "device3": 26
}

I would like to take only that device name and value whose value is equal to and above 25 (in this case device2 and device3)and then pass that value in subsequent method like creating and sending an alert message for each device name whose value is 25 or more.

How can I do that in logic app?

Upvotes: 0

Views: 3277

Answers (4)

Vijayamathankumar
Vijayamathankumar

Reputation: 3

Input taken as [
  {
    "deviceName": "\"device1\"",
    "value": "24"
  },
  {
    "deviceName": "\"device3\"",
    "value": "26"
  },
  {
    "deviceName": "\"device2\"",
    "value": "25"
  }
]

And simply arrived as below

  1. I have done Parse the JSON items { "device1": 24, "device2": 25, "device3": 26 }, then composed by using parse JSON o/p

  2. By using For each - Parsed Value, arrived the output 25 and above values 

    {
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Compose": {
                "inputs": [
                    {
                        "deviceName": "device1",
                        "value": "@{body('Parse_JSON')?['device1']}"
                    },
                    {
                        "deviceName": "device2",
                        "value": "@{body('Parse_JSON')?['device2']}"
                    },
                    {
                        "deviceName": "device3",
                        "value": "@{body('Parse_JSON')?['device3']}"
                    }
                ],
                "runAfter": {
                    "Parse_JSON": [
                        "Succeeded"
                    ]
                },
                "type": "Compose"
            },
            "For_each": {
                "actions": {
                    "Condition": {
                        "actions": {
                            "Append_to_array_variable": {
                                "inputs": {
                                    "name": "requiredValues",
                                    "value": "@items('For_each')"
                                },
                                "type": "AppendToArrayVariable"
                            }
                        },
                        "else": {
                            "actions": {}
                        },
                        "expression": {
                            "and": [
                                {
                                    "greaterOrEquals": [
                                        "@int(items('For_each')?['value'])",
                                        25
                                    ]
                                }
                            ]
                        },
                        "type": "If"
                    }
                },
                "foreach": "@outputs('Compose')",
                "runAfter": {
                    "Initialize_variable": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Initialize_variable": {
                "inputs": {
                    "variables": [
                        {
                            "name": "requiredValues",
                            "type": "array",
                            "value": []
                        }
                    ]
                },
                "runAfter": {
                    "Compose": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Parse_JSON": {
                "inputs": {
                    "content": {
                        "device1": 24,
                        "device2": 25,
                        "device3": 26
                    },
                    "schema": {
                        "properties": {
                            "device1": {
                                "type": "integer"
                            },
                            "device2": {
                                "type": "integer"
                            },
                            "device3": {
                                "type": "integer"
                            }
                        },
                        "type": "object"
                    }
                },
                "runAfter": {},
                "type": "ParseJson"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {}
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {}
        }
    }
}

Upvotes: 0

Deepak Shaw
Deepak Shaw

Reputation: 657

My Approach was similar, using Azure Function, only the code is a bit different, happy to share in case it helps someone.

public static async Task<IActionResult> Run(
        [HttpTrigger(AuthorizationLevel.Function,  "post", Route = null)] HttpRequest req,
        ILogger log)
    {
        var output = new List<Dictionary<string, string>>();
        try
        {
            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            
            if(string.IsNullOrEmpty(requestBody)) { return new OkObjectResult(output); }
            if(requestBody.Trim().ToLower() == "null") { return new OkObjectResult(output); }

            var values = JsonSerializer.Deserialize<Dictionary<string, string>>(requestBody);


            foreach ( var kvp in values )
            {
                var dict = new Dictionary<string, string>();
                dict.Add("Key", kvp.Key);
                dict.Add("Value", kvp.Value);
                output.Add(dict);
            }
        }
        catch (Exception ex)
        {
            log.LogError(ex.Message);
            return new BadRequestObjectResult(ex.Message);
            //throw;
        }
        return new OkObjectResult(output);

    }

Upvotes: 1

Skin
Skin

Reputation: 11197

In these situations, I find it much easier to write an Azure Function that does the work.

It'll ultimately keep your LogicApp cleaner than it would otherwise be with a whole heap of functionality to work around (perceived) limitations.

In the Azure Portal, go to the Azure Functions blade and create a new .NET HttpTrigger function with the following code ...

#r "Newtonsoft.Json"

using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

public static async Task<IActionResult> Run(HttpRequest req, ILogger log)
{
    string thresholdString = req.Query["threshold"];
    var threshold = int.Parse(thresholdString);

    string requestBody = await new StreamReader(req.Body).ReadToEndAsync();

    var jObject = JObject.Parse(requestBody);
    var filteredObject = new JObject(jObject.Properties().ToList().Where(x => (int)x.Value >= threshold));

    return new OkObjectResult(filteredObject);
}

... it assumes the JSON you pass in is along the lines of what you provided so be aware of that.

Now call it from your LogicApp like and you'll get the response you desire ...

Action

Action

Result

Result

Upvotes: 2

SwethaKandikonda
SwethaKandikonda

Reputation: 8234

Here is one of the workaround that worked for me. In order to filter the Json in well defined, easy format (i.e., devices whose value is greater than 25) and also for future use, we need to convert the json of format

{
  "device1": 24,
  "device2": 25,
  "device3": 26
}

to

[
  {
    "deviceName": "\"device1\"",
    "value": "24"
  },
  {
    "deviceName": "\"device3\"",
    "value": "26"
  },
  {
    "deviceName": "\"device2\"",
    "value": "25"
  }
]

We first need to convert the Json into array. This can be done in 2 ways Way -1 (using subString())

enter image description here

syntax in Method 1 step 1

substring(string(outputs('Compose')),1,sub(lastIndexOf(string(outputs('Compose')),'}'),1))

syntax in Method 1 step 2

array(split(string(outputs('Convert_To_Array_Method_1_step_1')),','))

Way -2 (using replace())

enter image description here

syntax in Method 2 step 1

replace(string(outputs('Compose')),'{','[')

syntax in Method 2 step 2

replace(string(outputs('Convert_To_Array_Method_2_step_1')),'}',']')

output:

enter image description here

You need to initialize an array variable in order to store the resultant json. We can now extract the values inside the json by taking either of syntax in Method 1 step 2or syntax in Method 2 step 2 outputs in For each connector.

enter image description here

  • In Extract values compose connector I'm trying to extract the values of the devices by taking substring expression.

    syntax in Extract values

    substring(item(),add(indexOf(item(),':'),1),sub(length(item()),add(indexOf(item(),':'),1)))

    Here I'm just extracting the values that are there after ':'.

  • The same goes with device names too. I'm extracting the device names that are there from index 0 to ':'.

    syntax in Formatted JSON

    {
"inputs": {
    "deviceName": "@substring(item(), 0, indexOf(item(), ':'))",
    "value": "@outputs('Extract_Value')"
    }
}

Then lastly I'm storing the resultant Formatted Json to a variable.

output:

enter image description here

Now I'm just parsing the the Formatted variable which gives me the results of device name and value.

enter image description here

In the next step I'm using Condition connector to check if the value greater than or equal to 25

syntax in Condition

int(items('For_each_2')['value'])

if the condition is true then it stores the value to Required Values.

output: enter image description here

Here is the code view of my logic app

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Compose": {
                "inputs": {
                    "device1": 24,
                    "device2": 25,
                    "device3": 26
                },
                "runAfter": {},
                "type": "Compose"
            },
            "Convert_To_Array_Method_1_step_1": {
                "inputs": "@substring(string(outputs('Compose')),1,sub(lastIndexOf(string(outputs('Compose')),'}'),1))",
                "runAfter": {
                    "Compose": [
                        "Succeeded"
                    ]
                },
                "type": "Compose"
            },
            "Convert_To_Array_Method_1_step_2": {
                "inputs": "@array(split(string(outputs('Convert_To_Array_Method_1_step_1')),','))",
                "runAfter": {
                    "Convert_To_Array_Method_1_step_1": [
                        "Succeeded"
                    ]
                },
                "type": "Compose"
            },
            "Convert_To_Array_Method_2_step_1": {
                "inputs": "@replace(string(outputs('Compose')),'{','[')",
                "runAfter": {
                    "Convert_To_Array_Method_1_step_2": [
                        "Succeeded"
                    ]
                },
                "type": "Compose"
            },
            "Convert_To_Array_Method_2_step_2": {
                "inputs": "@replace(string(outputs('Convert_To_Array_Method_2_step_1')),'}',']')",
                "runAfter": {
                    "Convert_To_Array_Method_2_step_1": [
                        "Succeeded"
                    ]
                },
                "type": "Compose"
            },
            "Final_Formated_JSON": {
                "inputs": "@variables('formatedArray')",
                "runAfter": {
                    "For_each": [
                        "Succeeded"
                    ]
                },
                "type": "Compose"
            },
            "Final_Values": {
                "inputs": "@variables('Required Values')",
                "runAfter": {
                    "For_each_2": [
                        "Succeeded"
                    ]
                },
                "type": "Compose"
            },
            "For_each": {
                "actions": {
                    "Append_to_array_variable": {
                        "inputs": {
                            "name": "formatedArray",
                            "value": "@outputs('Formated_JSON')"
                        },
                        "runAfter": {
                            "Formated_JSON": [
                                "Succeeded"
                            ]
                        },
                        "type": "AppendToArrayVariable"
                    },
                    "Extract_Value": {
                        "inputs": "@substring(item(),add(indexOf(item(),':'),1),sub(length(item()),add(indexOf(item(),':'),1)))",
                        "runAfter": {},
                        "type": "Compose"
                    },
                    "Formated_JSON": {
                        "inputs": {
                            "deviceName": "@substring(item(), 0, indexOf(item(), ':'))",
                            "value": "@outputs('Extract_Value')"
                        },
                        "runAfter": {
                            "Extract_Value": [
                                "Succeeded"
                            ]
                        },
                        "type": "Compose"
                    }
                },
                "foreach": "@outputs('Convert_To_Array_Method_1_step_2')",
                "runAfter": {
                    "Initialize_variable_to_store_the_formatted_Json_": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "For_each_2": {
                "actions": {
                    "Condition": {
                        "actions": {
                            "Append_to_array_variable_2": {
                                "inputs": {
                                    "name": "Required Values",
                                    "value": "@items('For_each_2')"
                                },
                                "runAfter": {},
                                "type": "AppendToArrayVariable"
                            }
                        },
                        "expression": {
                            "and": [
                                {
                                    "greaterOrEquals": [
                                        "@int(items('For_each_2')['value'])",
                                        25
                                    ]
                                }
                            ]
                        },
                        "runAfter": {},
                        "type": "If"
                    }
                },
                "foreach": "@body('Parse_JSON')",
                "runAfter": {
                    "Initialize_variable_to_store_required_values": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Initialize_variable_to_store_required_values": {
                "inputs": {
                    "variables": [
                        {
                            "name": "Required Values",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {
                    "Parse_JSON": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Initialize_variable_to_store_the_formatted_Json_": {
                "inputs": {
                    "variables": [
                        {
                            "name": "formatedArray",
                            "type": "array"
                        }
                    ]
                },
                "runAfter": {
                    "Convert_To_Array_Method_2_step_2": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Parse_JSON": {
                "inputs": {
                    "content": "@variables('formatedArray')",
                    "schema": {
                        "items": {
                            "properties": {
                                "deviceName": {
                                    "type": "string"
                                },
                                "value": {
                                    "type": "string"
                                }
                            },
                            "required": [
                                "deviceName",
                                "value"
                            ],
                            "type": "object"
                        },
                        "type": "array"
                    }
                },
                "runAfter": {
                    "Final_Formated_JSON": [
                        "Succeeded"
                    ]
                },
                "type": "ParseJson"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {},
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {}
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {}
}

Upvotes: 1

Related Questions