Question 211383 in Azure Question posted in
The official documentation to get you started can be found here.

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    just giving a clarifying answer to my own post, it looks like I had some wrong settings in ADF, I switched from bulk insert to copy command and it's now working as expected. Thank you dileeprajnarayanthumula for answering earlier.


  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search