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://learn.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

we can use contains in regular expression it will evaluate whether a key exist in JSON object or not. — MJ029, Mar 25 '21 at 17:32

Jason Nam · Answer 1 · 2020-08-21T17:48:40.197

3

If you're working with JSON:

item()?['Name']

edited Aug 21 '20 at 17:48

answered Aug 21 '20 at 17:35

Jason Nam

39
2

This works only if item() is null, this wont work if only 'Name' field is not available – shams.kool Aug 24 '20 at 07:18
@shams.kool can you add your logicApp code? "item()?['Name']" is working fine for me when "Name" is not available in json – Tomasz Kaniewski Aug 25 '20 at 09:29
This works when we have an input with a schema, like an HTTP request, not from the sources like SQL Server, Adding a JSON parser with a schema seems a better solution for me. – shams.kool Sep 08 '20 at 09:09
How to get value of "Name" when we use item()?['Name'] – GIRISH GMAIL Aug 07 '21 at 05:56
"item()?['Name']" also worked for me. To get "Name", you don't need anything, you just pass "item()?['Name']" and if there is anything there it gets returned, otherwise empty. Thanks Jason – Everton Oliveira Sep 09 '21 at 14:14

score 3 · Accepted Answer · answered Aug 25 '20 at 05:48

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.

ParseJSON is really a simpler solution to me, Thanks for pointing out. — shams.kool, Aug 27 '20 at 07:41

score 1 · Answer 3 · answered Apr 19 '21 at 18:43

1

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']"
        ]
      }
    ]
  },
...
..
.

answered Apr 19 '21 at 18:43

Dev

29
3

How can we use this with And another condition?? like I want to check whether Name is there or not AND Name is equal not to "xyz" – Sachin Jul 28 '21 at 14:43

MatnikR · Answer 4 · 2022-01-18T10:11:17.780

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

score 0 · Answer 5 · edited Mar 15 '22 at 19:22

0

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

see screenshot

edited Mar 15 '22 at 19:22

Jeremy Caney

7,102
69
48
77

answered Mar 09 '22 at 08:39

HM NG

1

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

Update 1

5 Answers5