Search code examples
jsonazureazure-data-factoryazure-synapse

Working with nested JSON objects in Azure Data Factory

I'm currently trying to call an API to grab data in JSON format and bring it into an Azure Synapse Data Warehouse. Below is a sample piece of the incoming JSON. The issue I'm running into I believe to be involving nesting the JSON. When I run the move data activity, the table in SQL only shows the group and id columns, while everything else is left null. Everything in the attribute object is left null.

I'm unsure what the mapping should look like, it has $['data'] as the collection reference, and every column listed in attribute as ['attribute']['columnName']. Every source I've found says this is correct, but it's not finding those values for some reason.

{
    "data": [
        {
            "group": "A",
            "id": 1,
            "attribute": {
                "userfirstname": "Greg",
                "userlastname": "Boydle",
                "useremailaddress": "[email protected]",
                
            }
        },
        {
            "group": "B",
            "id": 2,
            "attribute": {
                "userfirstname": "John",
                "userlastname": "Broglee",
                "useremailaddress": "[email protected]",
                
            }
        }
    ]
}

Any and all help is greatly appreciated, let me know if there are any clarifying questions that need answered.

Solution

  • Below is the mapping in the Azure data factory and synapse:

    ADF Mapping: enter image description here Synapse Mapping: enter image description here

    • Use the Collection reference as $[data] and import the schema.
    • Do not enable the advanced editor. Azure sql table Output: enter image description here Azure synapse dedicated pool output: enter image description here