JSON returning Null

Question

I'm trying to use Logic Apps to create an automation around Azure AD managed devices. I have two Graph API calls which are executed via a HTTP requests that return the desired results. I then parses these objects as a JSON file using the schema shown below. However, whenever I try to manipulate the JSON objects I get the following error:

ExpressionEvaluationFailed. The execution of template action 'For_each' failed: the result of the evaluation of 'foreach' expression '@Tomas Podoba ('Parse_JSON_-_Managed_Devices')?['body']?['value']' is of type 'Null'. The result must be a valid array.

JSON to get all managed devices
Graph query: https://graph.microsoft.com/beta/deviceManagement/managedDevices/?$select=id,userId,deviceName,userDisplayName,azureADDeviceId,managedDeviceName,emailAddress&$filter=operatingSystem eq 'windows'

{  
    "properties": {  
        "body": {  
            "properties": {  
                "@@odata.context": {  
                    "type": "string"  
                },  
                "@@odata.count": {  
                    "type": "integer"  
                },  
                "@@odata.nextLink": {  
                    "type": "string"  
                },  
                "value": {  
                    "items": {  
                        "properties": {  
                            "azureADDeviceId": {  
                                "type": "string"  
                            },  
                            "deviceName": {  
                                "type": "string"  
                            },  
                            "emailAddress": {  
                                "type": "string"  
                            },  
                            "id": {  
                                "type": "string"  
                            },  
                            "managedDeviceName": {  
                                "type": "string"  
                            },  
                            "userDisplayName": {  
                                "type": "string"  
                            },  
                            "userId": {  
                                "type": "string"  
                            }  
                        },  
                        "required": [  
                            "id",  
                            "userId",  
                            "deviceName",  
                            "userDisplayName",  
                            "azureADDeviceId",  
                            "managedDeviceName",  
                            "emailAddress"  
                        ],  
                        "type": "object"  
                    },  
                    "type": "array"  
                }  
            },  
            "type": "object"  
        }  
    },  
    "type": "object"  
}  

JSON to get all users
Graph query: https://graph.microsoft.com/beta/users?$select=id,displayName,mail,officeLocation&$filter=accountEnabled eq true

{  
    "properties": {  
        "body": {  
            "properties": {  
                "@@odata.context": {  
                    "type": "string"  
                },  
                "@@odata.nextLink": {  
                    "type": "string"  
                },  
                "value": {  
                    "items": {  
                        "properties": {  
                            "displayName": {  
                                "type": "string"  
                            },  
                            "id": {  
                                "type": "string"  
                            },  
                            "mail": {  
                                "type": "string"  
                            },  
                            "officeLocation": {  
                                "type": "string"  
                            }  
                        },  
                        "required": [  
                            "id",  
                            "displayName",  
                            "mail",  
                            "officeLocation"  
                        ],  
                        "type": "object"  
                    },  
                    "type": "array"  
                }  
            },  
            "type": "object"  
        }  
    },  
    "type": "object"  
}  

I would love if someone could help point me in the right direction.

Thanks in advance.

Answer 1

I had a similar issue, and the cause was the dynamic content syntax - when I used the dynamic content option for the property, I always got a NULL error result ("The execution of template action 'Apply_to_each_4' failed: the result of the evaluation of 'foreach' expression '@Tomas Podoba ('Parse_JSON')?['body']?['File']' is of type 'Null'. The result must be a valid array."). After playing around with the expression manually, I found that the dynamic content was putting an extra 'body' and '?'s between objects/arrays/properties and removing them gave me the expected result.

Didn't work (dynamic content expression): @Tomas Podoba ('Parse_JSON')?['body']?['File']
Did work: @Tomas Podoba ('Parse_JSON')['File']

Credit to smeadows for pointing me in the right direction from this Power Automate post: https://powerusers.microsoft.com/t5/General-Power-Automate/Parse-JSON-Null-Error-JSON-to-CSV/td-p/1251085

Answer 2

I have done some research and tests but haven’t found any solution. For such kind of JSON coding issue, it is better to create a Premier support ticket to resolve this issue more effectively. Here is the link: https://learn.microsoft.com/en-us/mem/get-support#the-support-experience

Answer 3

Hi @Omar Davis

Welcome to Microsoft Q&A! Thanks for posting the question.

You can leverage the "Use sample payload to generate schema" option of the "Parse Json" action to generate the schema.
To get the sample JSON payload you can execute the API here: https://developer.microsoft.com/en-us/graph/graph-explorer selecting the beta version drop down as your API URL has a beta path.

Regarding the error "ExpressionEvaluationFailed. The execution of template action 'For_each' failed: the result of the evaluation of 'foreach' expression '@Tomas Podoba ('Parse_JSON_-_Managed_Devices')?['body']?['value']' is of type 'Null'. The result must be a valid array." it means that the parson JSON action does not have the JSON value as the array values. If the data is returned correctly from the API but the schema is incorrect then the parsing will not happen correctly and the value for this action is NULL in your case. It's better to generate the schema using the sample input.

Hope the above helps. Feel free to get back to me if you need any assistance.

Answer 4

Hi,
Any update on this case?

I'm facing the same problem, valid JSON with a generated schema from actual data. Yet any reference to the array within the JSON returns null