How can we do schema validation in Azure Dataflow for Json having key fields and optional field names .

asked 2021-11-11T21:03:48.33+00:00
Abheesh S Kurup 1 Reputation point

In my case Json has an array of records and every record can have multiple fields but we need to ensure that all key fields are present.

Sample Json

{
"Parent": {
"First": {

"number": "01"
},
"Array": [{
"keyfield": "abc",
"optionalField1": "123",
"optionalField2": "1"
},
{
"keyfield": "abcd",
"optionalField4": "1234"
}],
"Last": {
"total": 1,
"last": "True"
}
}
}

I need to validate if all records inside Array have keyfield present else schema validation should fail.
I tried setting schema just with keyfield and enabled the checkbox for schema validation in dataflow. But later observed that only key field is getting mapped and optional fields went missing.

Any leads will be highly appreciated

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
6,267 questions
{count} votes

1 answer

Sort by: Most helpful
  1. answered 2021-11-12T20:16:51.2+00:00
    MartinJaffer-MSFT 23,996 Reputation points Microsoft Employee

    Hello @Abheesh S Kurup and welcome to Microsoft Q&A.

    If a property is missing, its value may be considered null. With that in mind,
    One options could be using a Lookup transformation to find any row whose keyfield value is null.

    The isNull(thing) function will be useful for that. It returns true when the thing is null, and false when not null. On the other side we fill in true().

    148909-image.png

    No comments