Getting an error message when query Cosmos, parse json and then iterate through the result so i can set the result of a field in a parameter that I can use

Question

Getting an error message when query Cosmos, parse json and then iterate through the result so i can set the result of a field in a parameter that I can use

Brian Hood 21

I am getting the following error in my logic app. The overall structure of the logic app is shown in the attached (#1). The "query cosmos document" step returns 1 document and that output is shown in the attached (#2). Then, I parse JSON and the raw output is shown in the attached #3. Everything looks good until I do my for next loop to set my variable so that I can use the variable later on in the logic app. #4 in the attached shows the schema that I used to define my parse json routine in #3. The Last step (for each 5) in the attached #5 is where I get my error. #5 shows the parameter I am using in the for next loop, and then the error message under it (see below). Do you have any ideas on what I could do to resolve the error?

ExpressionEvaluationFailed. The execution of template action 'For_each_4' failed: the result of the evaluation of 'foreach' expression '@Tomas Podoba ('Parse_JSON_2')?['items']' is of type 'Null'. The result must be a valid array.

Thanks
208582-logic-app-error.pdf

MayankBargali-MSFT 70,936 Reputation points Moderator

2022-06-08T09:35:32.843+00:00
@Brian Hood Thanks for reaching out. As per the attached file I can understand that you have the Array output as below, but I am not able to understand what is the transformation/expected output do you want from the below array? Can you share some examples what you are trying to achive?

[ { " RoomPresenseStatus ": "Left", "Room": "Homeroom", "id": "111111111111111" } ]

As per the error your Parse_JSON_2 object is becoming null therefore the for_each_4 action is getting failed. Have you verified what the output for Parse_JSON_2 was when you performed the test? I am not able to find the response for Parse_JSON_2 action in your attached file.
Brian Hood 21 Reputation points

2022-06-08T19:36:54.9+00:00

Hello, the expected output I want from the array is to be able to use the 3 attributes from that array in my logic app. To use those attributes, I am trying to use the "set attribute" function. However, when I am trying to set a variable with one of the attributes the system automatically creates a for next loop that I am forced to use. I really dont need a for next loop for this because i always retrieve 1 document in my query. However, I use the for next loop anyway because I do not know another way to get the attributes into variables to use later in my logic app in condition statements. The result of Parse_json_2 action is a json document that looks similiar to what you posted above, but it does not have the "fields" attribute anywhere in it, which may be why it is coming back as an error. Any ideas or is their a simpler way where I do not need to use the for next loop? Thanks

Accepted answer

0 additional answers

Your answer

MayankBargali-MSFT 70,936 Reputation points Moderator

2022-06-08T09:35:32.843+00:00

@Brian Hood Thanks for reaching out. As per the attached file I can understand that you have the Array output as below, but I am not able to understand what is the transformation/expected output do you want from the below array? Can you share some examples what you are trying to achive?

[ { " RoomPresenseStatus ": "Left", "Room": "Homeroom", "id": "111111111111111" } ]

As per the error your Parse_JSON_2 object is becoming null therefore the for_each_4 action is getting failed. Have you verified what the output for Parse_JSON_2 was when you performed the test? I am not able to find the response for Parse_JSON_2 action in your attached file.
Brian Hood 21 Reputation points

2022-06-08T19:36:54.9+00:00

Hello, the expected output I want from the array is to be able to use the 3 attributes from that array in my logic app. To use those attributes, I am trying to use the "set attribute" function. However, when I am trying to set a variable with one of the attributes the system automatically creates a for next loop that I am forced to use. I really dont need a for next loop for this because i always retrieve 1 document in my query. However, I use the for next loop anyway because I do not know another way to get the attributes into variables to use later in my logic app in condition statements. The result of Parse_json_2 action is a json document that looks similiar to what you posted above, but it does not have the "fields" attribute anywhere in it, which may be why it is coming back as an error. Any ideas or is their a simpler way where I do not need to use the for next loop? Thanks

Answer 1

MayankBargali-MSFT 70,936 Moderator

@Brian Hood Thanks for sharing more details. As you have mentioned that you are only retrieving one document so the array will always have one element so you need to have JSON schema without array and in the Content you should only specify the first element of the array.
You can leverage the Parse JSON that will create user-friendly fields or tokens. Now you can leverage these fields/token in the next action of your workflow.

You need to update the content with outputs('yourtriggername')[0] as I can see the trigger name as "Query documents v5 2" in your pdf screenshot so it should be outputs('Query_documents_v5_2')[0] but please validate the same at your end. For more details on outputs function please refer to this document.

Parse JSON Schema:

{  
    "properties": {  
        " RoomPresenseStatus ": {  
            "type": "string"  
        },  
        "Room": {  
            "type": "string"  
        },  
        "id": {  
            "type": "string"  
        }  
    },  
    "type": "object"  
}

You can see the Parse JSON output has created user-friendly fields that you can leverage in your next action.

Update:
Thanks for your time over the call. As discussed, we have leveraged the expression below in our Parse Json schema in your workflow along with the above schema.
Walkthrough how you can test and troubleshoot any failed run in your workflow.

body('Query_documents_v5_2')?['value'][0]

Feel free to get back to me if you need any assistance.

Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.

Brian Hood 21 Reputation points

2022-06-10T14:14:00.487+00:00

Thank you for your answer. When I input the output trigger that you mention, I get the following error message.

InvalidTemplate. Unable to process template language expressions in action 'Parse_JSON_2' inputs at line '0' and column '0': 'The template language expression 'outputs('Query_documents_V5')[0]' cannot be evaluated because property '0' cannot be selected. Object properties can only be selected by names. Please see https://aka.ms/logicexpressions for usage details.'

If I remove the [0], then it appears to work fine and return the json record, but when I do a set attribute function and select one of the attributes returned from Parse json, I get an error that the attribute is null even though the parse json step returns a value.

Thanks
MayankBargali-MSFT 70,936 Reputation points Moderator

2022-06-13T05:05:52.657+00:00

@Brian Hood Can you please confirm what is the output of Query_documents_V5 action? Is it an array of JSON objects or only a single JSON object?
Have you verified whether from run history Query_documents_V5 action whether it is coming null or empty then the schema will be changed or need to add one condition to check whether Query_documents_V5 output is null or empty.
Brian Hood 21 Reputation points

2022-06-13T15:19:13.737+00:00

Hello, the output of Query_documents_V5 step is below. It always returns only 1 record without any NULL in the attributes. Maybe the brackets are creating an issue?

[
{
"Room": "Bathroom",
"RoomPresenseStatus": "Left the Room",
"id": "e31a4119-3cba-4d02-8391-9165ba052bdb",
"_rid": "waAVAJcaL9t3OQEAAAAAAA==",
"_self": "dbs/waAVAA==/colls/waAVAJcaL9s=/docs/waAVAJcaL9t3OQEAAAAAAA==/",
"_etag": "\"e100959d-0000-0700-0000-62a2a9d00000\"",
"_attachments": "attachments/",
"_ts": 1654827472
}
]
MayankBargali-MSFT 70,936 Reputation points Moderator

2022-06-15T14:03:35.753+00:00

@Brian Hood As per the sample shared the workflow should work and I have tested the same. Just a few fields will not be captured as your Json array has new fileds this time than previously. Can you refer to my private comment so I can connect offline and review your workflow and assist you further.

Share via

Getting an error message when query Cosmos, parse json and then iterate through the result so i can set the result of a field in a parameter that I can use

0 additional answers

Your answer