Azure Logic App: Checking whether property exists in a json object

user_6462225 0
Viewed 7328

I have a JSON coming from an 'SQLServer GetRecords (V2)' action like the following. Unfortunately, the response will not contain fields with null values. In my example, the 'Name' field is null for some items.

[
  {
    "@odata.etag": "",
    "ItemInternalId": "378fd3bc-0cd4-4171-8e7d-462461086580",
    "RowID": 1,
    "Name": "1234"
  },
  {
    "@odata.etag": "",
    "ItemInternalId": "378fd3bc-0cd4-4171-8e7d-462461086580",
    "RowID": 1
  }, ...
}

I want to iterate these items and pass each item to another HTTP endpoint.

When I use item()['Name'] or item()?['Name'] to access the name field it will fail for the second item saying

The template language expression cannot be evaluated because property 'Name' doesn't exist, available properties are ...

I see a lot of people using xpath function combined with xml function to retrieve the value.

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference

But when I use some think like xpath(xml(item()), '/Name'), it will throw an error saying

The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'JSON root object has property '@odata.etag' that will be converted to an attribute. A root object cannot have any attribute properties. Consider specifying a DeserializeRootElementName. Path '['@odata.etag']'.'. Please see https://aka.ms/logicexpressions#xml for usage details.

Update 1

I got this working with the following expression, I really do not like this

first(xpath(xml(addProperty(json('{}'), 'obj', item())), '//obj/Name[1]/text()'))

Is there any other easy way to fix my problem. Thanks in advance

azure-logic-apps
5 Answers

If you're working with JSON:

item()?['Name']
user_141446810

For this problem, it seems you get "Name" value in "For each" loop from the the response of "SQLServer GetRecords (V2)" directly. We can just use a "Parse JSON" action to solve this problem easily. Please refer to my logic app below:

1. I initialize a variable store the same json data as yours to simulate the response from "SQLServer GetRecords (V2)".

enter image description here

2. Then add "Parse JSON" action to parse the jsondata. We can click the "Use sample payload to generate schema" button and copy jsondata into it. It will generate the schema automatically. The schema shown like below (please note: the schema specifies whether these fields are required in required property).

{
    "items": {
        "properties": {
            "@@odata.etag": {
                "type": "string"
            },
            "ItemInternalId": {
                "type": "string"
            },
            "Name": {
                "type": "string"
            },
            "RowID": {
                "type": "integer"
            }
        },
        "required": [
            "@@odata.etag",
            "ItemInternalId",
            "RowID"
        ],
        "type": "object"
    },
    "type": "array"
}

enter image description here

3. Now use "For each" to loop the body from "Parse JSON" and set the "Value" box with Name property from "Parse JSON".

enter image description here

4. Run the logic app, it works fine without any error message. enter image description here enter image description here

user_110992130

If anyone is looking to check if a JSON property exists, you can use a condition with not & empty expressions like this -

  "expression": {
    "not": [
      {
        "empty": [
          "@JSONobject()?['keyName']"
        ]
      }
    ]
  },
...
..
.
user_70752270

... it seems like an old but still valid question.

What you can do is actually use "configure run after". Initialise Variable type Boolean, then on the next box try to set the variable to expression "not(empty(<your property here))". This action will fail if the property does not exists. So follow with another "Set Variable" to expression "false". And on this action configure run after to be run if previous action fails. If next item is "Check condition" your test variable will be false if the property did not existed or was of empty value.

user_51771230

It is quite simple. You can just use the Null function in the Logic App:

see screenshot

user_184167100
Related