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

Question

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

Answer 1

If you're working with JSON:

item()?['Name']

Answer 2

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)".

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"
}

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

4. Run the logic app, it works fine without any error message.