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

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

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.
11,234 questions
{count} votes

1 answer

Sort by: Most helpful
  1. MartinJaffer-MSFT 26,226 Reputation points
    2021-11-12T20:16:51.2+00:00

    Hello @Anonymous 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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.