Copy complex nested json to multiple sql tables

Svgk, Raju (Nokia - IN/Chennai) 6 Reputation points
2022-06-19T16:09:23.977+00:00

I have the following json data in ADL.

{  
        "stores": [  
            {  
                "items": [  
                    {  
                        "name": "Item 1",  
                        "price": 20.04  
                    },  
                    {  
                        "name": "Item 2",  
                        "price": 22.06  
                    },  
                    {  
                        "name": "Item 3",  
                        "price": 24.08  
                    }  
                ],  
                "name": "Store 1"  
            },  
            {  
                "items": [  
                    {  
                        "name": "Item 4",  
                        "price": 10.04  
                    },  
                    {  
                        "name": "Item 5",  
                        "price": 12.06  
                    },  
                    {  
                        "name": "Item 6",  
                        "price": 14.08  
                    }  
                ],  
                "name": "Store 2"  
            }  
        ]  
    }  

Need to copy this data to SQL DB with two different tables.

CREATE TABLE dbo.Stores (  
  Store_ID int IDENTITY (1,1) NOT NULL,  
  Store_Name VARCHAR(32) NOT NULL,  
  CONSTRAINT PK_Stores PRIMARY KEY (Store_ID),  
  CONSTRAINT UK_Stores UNIQUE(Store_Name)  
);  
  
CREATE TABLE dbo.Items (  
  Item_ID int IDENTITY (1,1) NOT NULL,  
  Item_Name VARCHAR(32) NOT NULL,  
  Item_Price money NULL,  
  Item_Store_ID int NOT NULL,  
  CONSTRAINT PK_Items PRIMARY KEY (Item_ID),  
  CONSTRAINT UK_Items UNIQUE(Item_Name),  
  CONSTRAINT FK_Items_Stores FOREIGN KEY (Item_Store_ID) REFERENCES dbo.Stores (Store_ID)  
);  

I would like to create an entry in the Stores table first based on Store_Name and create Items in Items table with Item_Store_ID as Store_ID in Stores table for that Store_Name.

How to achieve this in ADF pipeline?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,249 questions
0 comments No comments
{count} vote

1 answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 34,011 Reputation points Microsoft Employee
    2022-06-21T19:43:04.767+00:00

    Hi @Svgk, Raju (Nokia - IN/Chennai) ,
    Welcome to Microsoft Q&A platform and thanks for posting your question.

    As I understand your requirement, you want to load the data of nested JSON into two tables in SQL DB. Please let me know if my understanding is incorrect.

    You need to use Mapping Data flow to achieve this .

    1. Create the dataset pointing to the JSON file and select 'Array of documents' in JSON settings under Source option.

    213506-image.png

    2. Create New branch from the source transformation and add flatten activity in each branch

    3. For stores table, use 'Stores' array to unrollby and select the name column .

    213536-image.png

    4. For Items table, use 'Stores.Items' array to unrollby and select name and price columns

    213497-image.png

    5. In the sink transformation , create dataset pointing to stores and items table respectively and disable auto-mapping . While mapping manually, don't map identity columns in stores and items table. Also, one point to be taken care is money datatype for Item_Price in sink table is not compatible in mapping.

    213537-image.png

    Once the data gets copied into the two tables, you can create triggers inside SQL to update Item_Store_ID from Items table according to the corresponding Store_ID in Stores table.

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.