I have multiple JSON files and use ADF dataflow to exclude the "record" and "contractNumbers" objects, retaining only the {"report":{"metadata":{...}}} objects. However, I am unable to structure the output JSON as expected. What other methods can resolve the problem of excluding unnecessary objects?

{
   "record": {
        "report": {
            "metadata": {
                "accessionNumber": "2004",
                "reportDate": {
                    "date": "2025-01-01"
                }
        },
        "contractNumbers": {
            "contractNumber": {
                "commercial": "ANS1002",
                "residental": "CA1191"
            }
        }
    }
 }
}

The result will be

{
 "report": {
        "metadata": {
            "accessionNumber": "2004",
            "reportDate": {
                "date": "2025-01-01"
            }
        }
    }

Filterter transformation:

Derivedved transformation:

I have multiple JSON files and use ADF dataflow to exclude the "record" and "contractNumbers" objects, retaining only the {"report":{"metadata":{...}}} objects. However, I am unable to structure the output JSON as expected. What other methods can resolve the problem of excluding unnecessary objects?

{
   "record": {
        "report": {
            "metadata": {
                "accessionNumber": "2004",
                "reportDate": {
                    "date": "2025-01-01"
                }
        },
        "contractNumbers": {
            "contractNumber": {
                "commercial": "ANS1002",
                "residental": "CA1191"
            }
        }
    }
 }
}

The result will be

{
 "report": {
        "metadata": {
            "accessionNumber": "2004",
            "reportDate": {
                "date": "2025-01-01"
            }
        }
    }

Filterter transformation:

Derivedved transformation:

• azure-data-factory

• Are running the above dataflow in a loop for the multiple JSON files? – Rakesh Govindula Commented Feb 16 at 13:20
• The screenshots above are from dataflow. But not know how exclude the record element. – thichxai Commented Feb 16 at 14:25
• Yeah, you have mentioned that you are dealing with multiple JSON files. Are you using loop for all files and is the above dataflow inside the the loop to handle each JSON file? – Rakesh Govindula Commented Feb 16 at 18:38
• I don't use forEach transformation on the pipeline. – thichxai Commented Feb 16 at 18:57

1 Answer 1

You can the combination of derived column and select transformations to achieve your requirement.

To select only the required objects from the reports object, first use a derived column transformation with below expression.

report - @(metadata=record.report.metadata)

Here, this will create an additional column named report which contains the required objects.

Now, use select transformation with a Rule-based mapping to select only the report column as shown below. Give the expression as name=='report'.

Now, you can see the required objects.

You can add your filter transformation for the accessionNumber before the derived column transformation.

I have used 3 JSON source files, and you can see there are 3 JSON objects in the result JSON file.