Flatten complex array from Facebook using Azure data factory

Question

Using the REST connector in Azure Data Factory, I am trying to fetch the Facebook campaign details. In the pipeline, I have a web activity followed by copy activity. In the mapping section, I can see only the three columns (Id, name, status) from the first array and not getting those columns listed inside the second array.

graph.facebook.com

data factory mapping

Is there a way to get the columns listed inside the array? I also tried creating a data flow taking the Json file as source and then used the flatten transformation, still I cannot see the columns related to campaigns. Any help is appreciated. Thanks again.

Answer 1

@Leon Yue, I found a way to do that.

Step 1: 1. Copy Facebook campaign data using the REST connector and save as JSON in Azure Blob.

Step 1: copy activity to extract FB data as JSON and save in Blob

Step 2: 2. Create a Data flow, considering the JSON file from Blob as the source.

Step 2: Data flow task

Step 3: Create a JSON schema and save it in your desktop with the insights array in the first row(which has all the column values) As per your previous comments, I created the JSON schema such that ADF will consider the first object/ JSON array as the JSON schema.

Step 4: In the Data flow - Source dataset, map the JSON schema using the 'Import schema' option from sample file. Step 4: Import schema

Now you will be able to see all the columns from the array. All columns

Flatten JSON

Answer 2

I tested and find that Data Factory will consider the first object/JSON array as the JSON schema.

If you can adjust the JSON data, then the "insights" can be recognized:

Schema:

If you can't, then the "insights" column will be missed:

In this case, there isn't a way to get all the columns listed inside the array.

HTH.