Parse JSON - validation failed for null values - how to avoid?

[Medoomi]

I have a PowerApps instant cloud flow which receives a JSON array as a string

I am attempting to parse the JSON, which mostly works... except when null values appear in the string, at which point the validation fails. The schema it uses is as follows:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Address1stLine": {
                "type": "string"
            },
            "Address2ndLine": {
                "type": "string"
            },
            "City": {
                "type": "string"
            },
            "ContractType": {
                "type": "string"
            },
            "CreatedOn": {
                "type": "string"
            },
            "Customer": {
                "type": "string"
            },
            "OperatingUnit": {
                "type": "string"
            },
            "ProjectName": {
                "type": "string"
            },
            "RSMeansAuditDate": {
                "type": "string"
            },
            "RequestNumber": {
                "type": "string"
            },
            "SalespersonName": {
                "type": "string"
            },
            "ScopeOfWork": {
                "type": "string"
            },
            "State": {
                "type": "string"
            },
            "Zip": {
                "type": "string"
            }
        },
        "required": [
            "Address1stLine",
            "Address2ndLine",
            "City",
            "ContractType",
            "CreatedOn",
            "Customer",
            "OperatingUnit",
            "ProjectName",
            "RSMeansAuditDate",
            "RequestNumber",
            "SalespersonName",
            "ScopeOfWork",
            "State",
            "Zip"
        ]
    }
}

Is my problem that the fields are marked as "required?"

Or how might I parse a JSON string with null values included in it?

[Pstork1 · Accepted solution]

To handle null values you need to change the "type": setting for that property from "string" to ["string", "null"].  This will let that property be either a string or null.  You should also remove that particular property from the list of required columns.

[Medoomi]

Thanks so much @Pstork1 !

It looks like this will work... except after changing the Schema of the Parse JSON, I cannot find the various data fields in the dynamic content picker (eg. Address1stLine). All that is available is "Item", and "Body."

Is there a way still to have those fields available in the dynamic content picker? I suppose I can figure out how to enter the various expressions manually, but it would be nice to have the more user-friendly labeling of the dynamic picker...

thanks!

[Pstork1]

The change I suggested should not effect the appearance of the data in the dynamic content dialog.  Something is off in your JSON Schema.  Try creating it from scratch and making the changes I suggested again.

[Medoomi]

Thank you so very much again for your reply @Pstork1 

I have tried reconstructing the schema again, with the same result that the dynamic content becomes unavailable.

The schema as I have it is as follows. I have tried with and without "required" fields being present.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Address1stLine": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "Address2ndLine": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "City": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "ContractType": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "CreatedOn": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "Customer": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "OperatingUnit": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "ProjectName": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "RSMeansAuditDate": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "RequestNumber": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "SalespersonName": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "ScopeOfWork": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "State": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "Zip": {
                "type": [
                    "string",
                    "null"
                ]
            }
        },
        "required": []
    }
}

Can you see anything I am doing wrong?

[Pstork1]

Can you provide a small sample of the data you are trying to parse?

[Medoomi]

Sure! It only sends one record. Here is some sample data from the last run:

[{"Address1stLine":"6001 E. 600 S.","Address2ndLine":"","City":"Montgomery","ContractType":"Gordian Zone #5","CreatedOn":"2/3/2021","Customer":"something customer","OperatingUnit":"company name","ProjectName":"Mitchell - Warehouse Bldg. ","RSMeansAuditDate":null,"RequestNumber":"","SalespersonName":"John Smith","ScopeOfWork":"","State":"Indiana","Zip":"47558"}]

[Pstork1 · Accepted solution]

I did some testing and it looks like some things have changed in the filtering of dynamic content.  Allowing "null" values will indeed filter the values out of the dynamic content dialog.  But the JSON representation of the field can still be entered and it will return the value, either the real one or the null.  So here's the workaround. Don't add the ["string", "null"] changes to the schema until after you have added the fields in any later statements that use the field.  Just add it before you run it the first time.  The flow will work, even though the fields won't show in the editor after making the change.

[Medoomi]

Perfect, thank you so very much!

[arindam40]

Hello Folks,

I am having exactly similar issues parsing Jason values. I am trying to call Graph API & get Announcement messages & trying to save in SharePoint list. I'll show my flow details, but here is the error I get:

The execution of template action 'Apply_to_each_2' failed: the result of the evaluation of 'foreach' expression '@body('Parse_JSON')?['body']?['value']' is of type 'Null'. The result must be a valid array.

Calling https://graph.microsoft.com/v1.0/admin/serviceAnnouncement/messages successuully, in Graph explorer & in the FLow. Till there its Ok. 

Then I created the JSON Schema by pasting the downloaded the value. Here is the schema:

{
"type": "object",
"properties": {
"statusCode": {
"type": "integer"
},
"headers": {
"type": "object",
"properties": {
"Transfer-Encoding": {
"type": "string"
},
"Vary": {
"type": "string"
},
"Strict-Transport-Security": {
"type": "string"
},
"request-id": {
"type": "string"
},
"client-request-id": {
"type": "string"
},
"x-ms-ags-diagnostic": {
"type": "string"
},
"OData-Version": {
"type": "string"
},
"Date": {
"type": "string"
},
"Content-Type": {
"type": "string"
},
"Content-Length": {
"type": "string"
}
}
},
"body": {
"type": "object",
"properties": {
"@@odata.context": {
"type": "string"
},
"@@odata.nextLink": {
"type": "string"
},
"value": {
"type": [
"array",
"null"
],
"items": {
"type": "object",
"properties": {
"startDateTime": {
"type": "string"
},
"endDateTime": {
"type": "string"
},
"lastModifiedDateTime": {
"type": [
"string",
"null"
]
},
"title": {
"type": "string"
},
"id": {
"type": "string"
},
"category": {
"type": "string"
},
"severity": {
"type": "string"
},
"tags": {
"type": "array",
"items": {
"type": "string"
}
},
"isMajorChange": {
"type": "boolean"
},
"actionRequiredByDateTime": {},
"services": {
"type": [
"string",
"null"
],
"items": {
"type": "string"
}
},
"expiryDateTime": {},
"hasAttachments": {
"type": [
"boolean",
"null"
]
},
"viewPoint": {},
"details": {
"type": [
"string",
"null"
]
},
"body": {
"type": "object",
"properties": {
"contentType": {
"type": "string"
},
"content": {
"type": "string"
}
}
}
},
"required": [
"startDateTime",
"endDateTime",
"lastModifiedDateTime",
"title",
"id",
"category",
"severity",
"tags",
"isMajorChange",
"actionRequiredByDateTime",
"services",
"expiryDateTime",
"hasAttachments",
"viewPoint",
"details",
"body"
]
}
}
}
}
}
}

This is how my flow looks like

Please let me know, what changes are required. I can try the solution mentioned above, but where all should I make these changes.

Any help would be great.

Thanks

Arindam

[Pstork1]

As the error message says, one of the values in your array is null and can't be processed.  There are two ways to fix it.  First, you could add a filter array before using the array in the inner apply to Each 2 loop to filter out elements with Null values. Or you could add a Coalesce() function around the array used in the loop to substitute an empty array if the array being processed is null.  Something like this

coalesce(body('Parse_JSON')?['body']?['value'],createArray(''))

[arindam40]

Hi pstork1,

This issue is resolved now. I dont know why, but the schema I generated to parse JSON was not proper.

Thanks

Arindam

[mkilp91]

Hi @Medoomi and @Pstork1,

I have another solution for writing your schemas to allow null values while maintaining the ability to use dynamic content without having to manually type the references.

While the method of setting your property types in an array will pass the schema validation, Power Automate prefers a slightly different syntax. Try using the "anyOf" syntax instead. For example:

{
  "property_name": {
    "anyOf": [
      {"type": "string"},
      {"type": "null"}
    ]
  },
  "required": [
    "property_name"
  ]
}

It's a bit annoying because it's essentially the same thing. However, using "anyOf" (in my experience) allows you to select the dynamic content in the actions that follow your Parse JSON action.

I hope this helps!

[Pstork1]

Interesting workaround. I'll try it the next time I get a chance. I haven't seen an issue with the regular array forcing manual entry of the JSON. But you do have to make sure the logic allows for null values. 

[heinvr]

I sincerely appreciate it. I was able to solve my problem.

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.type": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "Claims": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "DisplayName": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "Email": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "Picture": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "Department": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "JobTitle": {
                "type": [
                    "string",
                    "null"
                ]
            }
        },
        "required": [
            "@@odata.type",
            "Claims",
            "DisplayName",
            "Email",
            "Picture",
            "Department",
            "JobTitle"
        ]
    }
}