Add REST API Json response data into Azure SQL database using Azure Data Factory

Question

Hello Everyone,
I want to insert data in multiple tables using single REST API Json response into Azure SQL Database using Azure Data Factory. I am not able to insert all the list of data in different tables using stored procedure.

For e. g., I have 3 tables DemoTbl, Items, ChildItems and want to insert data into those 3 tables, DemoTbl with list of Items and childItems also. I have received json in nested class format, like below json:
{ "Id": 1, "Name": "Test", "Items": [ { "ItemId": 1, "ItemName": "Item 1", "ChildItems":[ { "ChildItemId": 11, "ChildItemName": "Child Item 1" }, { "ChildItemId": 12, "ChildItemName": "Child Item 2" } ], "IsActive": true }, { "ItemId": 2, "ItemName": "Item 2", "ChildItems":[ { "ChildItemId": 23, "ChildItemName": "Child Item 3" }, { "ChildItemId": 24, "ChildItemName": "Child Item 4" } ], "IsActive": true } ] }

Answer 1

@Bhavika Nimbark

You can flatten the Json hierarchy first and then insert the data into tables.
Please check if this works for you in your stored procedure logic. If you have further questions or issues please let us know.

DECLARE @json NVARCHAR(MAX);  
SET @json = N'{ "Id": 1, "Name": "Test", "Items": [ { "ItemId": 1, "ItemName": "Item 1", "ChildItems":[ { "ChildItemId": 11, "ChildItemName": "Child Item 1" }, { "ChildItemId": 12, "ChildItemName": "Child Item 2" } ], "IsActive": true }, { "ItemId": 2, "ItemName": "Item 2", "ChildItems":[ { "ChildItemId": 23, "ChildItemName": "Child Item 3" }, { "ChildItemId": 24, "ChildItemName": "Child Item 4" } ], "IsActive": true } ] }';  
  
SELECT Id, Name, ItemId, ItemName, ChildItemId, ChildItemName  
FROM OPENJSON(@json)    
  WITH (  
    Id INT 'strict $.Id',  
    Name NVARCHAR(50) '$.Name',  
    Items NVARCHAR(max) '$.Items'    AS JSON  
  )  
OUTER APPLY OPENJSON(Items)  
  WITH (ItemId NVARCHAR(8) '$.ItemId', ItemName NVARCHAR(50) '$.ItemName', ChildItems NVARCHAR(max) '$.ChildItems'    AS JSON)   
  OUTER APPLY OPENJSON(ChildItems)  
  WITH (ChildItemId NVARCHAR(8) '$.ChildItemId', ChildItemName NVARCHAR(50) '$.ChildItemName') ;  

Output:

--

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.