Complex Json mapping with Azure data factory

Question

I have to map an output Json format of an API that contains multiple collections, nested arrays... I well receive the Json file, then i use the activity copy to transform Json to Parquet file, in the mapping settings, i manually create the complex mapping and i get the data, it works fine but... The issue is that the Cross join is not applied good, means give me for the nested array (ChildRows) only the first index.

so in the above example will give me only first values of rowuid_Child_value1 of ChildRows (array) and not the values of the second one (rowuid_Child_value2)...

this is the mapping in adf

I have tried also with advanced editor, dynamics mapping but not the expected result. Do you have any advice please or same situations in ADF? i can't use the DataFlow as we don't have it our project with adf so this is not an option. Thank you for helping...

Answer 1

You can achieve your requirement by following below procedure:

Go to Mapping of copy activity Enable Advanced editor and enter $['Rows'][0]['ChildRows'] Collection reference as shown below:

Then mapping will set as below:

With above mapping the Json data will modify as shown below:

Rows[0]['RowUID']	FormNumber	IncidentDate	IncidentSummary	RecoveryRequired	BusinessEntity	Classification	Rows[0]['EntityName']	Rows[0]['ParentRowUID']	Item	LeftRowUID	Rows[0]['ChildRows'][0]['RowUID']	Ordinal	Type	Rows[0]['ChildRows'][0]['ParentRowUID']	IsReportableEnvDamage	Rows[0]['ChildRows'][0]['EntityName']	Consequence	Code	Reportable
rowuid_value	xxx	date	data	1	data	data	xxx	rowuid_value	xxx	rowuid_value	rowuid_Child_value1	0	xxx	xxx	0	xxx	xxx	xxx	xxx
rowuid_value	xxx	date	data	1	data	data	xxx	rowuid_value	xxx	rowuid_value	rowuid_Child_value2	0	xxx	rowuid_value	1	xxx	xxx	xxx	xxx

Here is the pipeline Json for your reference:

{
    "name": "pipeline2",
    "properties": {
        "activities": [
            {
                "name": "Copy data1",
                "type": "Copy",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "JsonSource",
                        "storeSettings": {
                            "type": "AzureBlobFSReadSettings",
                            "recursive": true,
                            "enablePartitionDiscovery": false
                        },
                        "formatSettings": {
                            "type": "JsonReadSettings"
                        }
                    },
                    "sink": {
                        "type": "DelimitedTextSink",
                        "storeSettings": {
                            "type": "AzureBlobFSWriteSettings"
                        },
                        "formatSettings": {
                            "type": "DelimitedTextWriteSettings",
                            "quoteAllText": true,
                            "fileExtension": ".txt"
                        }
                    },
                    "enableStaging": false,
                    "translator": {
                        "type": "TabularTranslator",
                        "mappings": [
                            {
                                "source": {
                                    "path": "$['Rows'][0]['RowUID']"
                                },
                                "sink": {
                                    "name": "Rows'][0]['RowUID"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['Rows'][0]['FormNumber']"
                                },
                                "sink": {
                                    "name": "FormNumber"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['Rows'][0]['IncidentDate']"
                                },
                                "sink": {
                                    "name": "IncidentDate"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['Rows'][0]['IncidentSummary']"
                                },
                                "sink": {
                                    "name": "IncidentSummary"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['Rows'][0]['RecoveryRequired']"
                                },
                                "sink": {
                                    "name": "RecoveryRequired"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['Rows'][0]['BusinessEntity']"
                                },
                                "sink": {
                                    "name": "BusinessEntity"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['Rows'][0]['Classification']"
                                },
                                "sink": {
                                    "name": "Classification"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['Rows'][0]['EntityName']"
                                },
                                "sink": {
                                    "name": "Rows'][0]['EntityName"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['Rows'][0]['ParentRowUID']"
                                },
                                "sink": {
                                    "name": "Rows'][0]['ParentRowUID"
                                }
                            },
                            {
                                "source": {
                                    "path": "$['Rows'][0]['Item']"
                                },
                                "sink": {
                                    "name": "Item"
                                }
                            },
                            {
                                "source": {
                                    "path": "['LeftRowUID']"
                                },
                                "sink": {
                                    "name": "LeftRowUID"
                                }
                            },
                            {
                                "source": {
                                    "path": "['RowUID']"
                                },
                                "sink": {
                                    "name": "Rows'][0]['ChildRows'][0]['RowUID"
                                }
                            },
                            {
                                "source": {
                                    "path": "['Ordinal']"
                                },
                                "sink": {
                                    "name": "Ordinal"
                                }
                            },
                            {
                                "source": {
                                    "path": "['Type']"
                                },
                                "sink": {
                                    "name": "Type"
                                }
                            },
                            {
                                "source": {
                                    "path": "['ParentRowUID']"
                                },
                                "sink": {
                                    "name": "Rows'][0]['ChildRows'][0]['ParentRowUID"
                                }
                            },
                            {
                                "source": {
                                    "path": "['IsReportableEnvDamage']"
                                },
                                "sink": {
                                    "name": "IsReportableEnvDamage"
                                }
                            },
                            {
                                "source": {
                                    "path": "['EntityName']"
                                },
                                "sink": {
                                    "name": "Rows'][0]['ChildRows'][0]['EntityName"
                                }
                            },
                            {
                                "source": {
                                    "path": "['Consequence']"
                                },
                                "sink": {
                                    "name": "Consequence"
                                }
                            },
                            {
                                "source": {
                                    "path": "['Code']"
                                },
                                "sink": {
                                    "name": "Code"
                                }
                            },
                            {
                                "source": {
                                    "path": "['Reportable']"
                                },
                                "sink": {
                                    "name": "Reportable"
                                }
                            }
                        ],
                        "collectionReference": "$['Rows'][0]['ChildRows']",
                        "mapComplexValuesToString": false
                    }
                },
                "inputs": [
                    {
                        "referenceName": "Json2",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "DelimitedText1",
                        "type": "DatasetReference"
                    }
                ]
            }
        ],
        "annotations": []
    }
}