json data in ADF

Question

Hey Team

How to filter data in adf pipeline from json file

where bar <> "data"

{
  "foo": {
    "bar": "data",
    "baz": "data"
  }
}

Answer 1

Hi Vineet S ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

I understand that you are trying to filter data of json based on certain condition.

You could try to leverage mapping dataflow for this purpose in Azure data factory.

• Flatten the json data using flatten transformation first, however it would require to select an array to unroll by.
• Since the json which you shared doesn't contain any array , you could use aggregate transformation first where in aggregate tab, use collect() function to convert the object into array first and then use flatten transformation to flatten the array
• Now use filter transformation to filter the record based on the condition that you want.
• To write it back in json format to sink file, use derived column transformation and create the json using add column and add subcolumn option.

Relevant resources: How to filter out records based on pipeline parameter using mapping dataflow

How to convert a JSON record into NESTED JSON using mapping dataflow

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou