Parse JSON data and insert into multiple tables in SQL Server

Question

I have a JSON which contains the customer information as following format

{
  "customerdata": [
    {
      "customerID": "abcsd112-1234-4c01-abcd-bb2fb084dc52",
      "customerDetails": [
        {
          "fieldId": "100",
          "fieldValue": "ABC"
        },
        {
          "fieldId": "101",
          "fieldValue": "TESTCUSTOMER001"
        },
        {
          "fieldId": "102",
          "fieldValue": "1000"
        },
        {
          "fieldId": "103",
          "fieldValue": "TESTNAME"
        }     
            
      ]
    },
    {
      "customerID": "cdfsd112-4c01-45d7-abcd-9c9662d4ca30",
      "customerDetails": [
        {
          "fieldId": "100",
          "fieldValue": "CDE"
        },
        {
          "fieldId": "101",
          "fieldValue": "TESTCUSTOMER002"
        },
        {
          "fieldId": "102",
          "fieldValue": "1002"
        },
        {
          "fieldId": "103",
          "fieldValue": "TESTNAME2"
        }    
      ]
    }    
  ]
}

From this JSON data, I want insert data into two separate tables. In such a way that The customer ID detail to one table (CustomerDetails) and customer field details (customerDetails json node) to another table (CustomerFieldData) with foreign key reference to the first table(CustomerID).

So following are the table required

1. CustomerDetails table structure where customerID from JSON should be inserted to CustomerUniqueGUID column and customer name will be from field id 101 of customerDetails json node

   CREATE TABLE [CustomerDetails](
   [CustomerID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
   [CustomerUniqueGUID] UNIQUEIDENTIFIER NOT NULL,
   [CustomerName] [varchar](100) NULL, 
   [IsActive] [bit] NULL)
   

2. CustomerFieldData Table

   CREATE TABLE [CustomerFieldData](
       [CustomerFieldDataID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
       [CustomerID] [bigint] NOT NULL FOREIGN KEY REFERENCES [CustomerDetails] ([CustomerID]), 
       [FieldID] [varchar](100) NOT NULL,
       [FieldValue] [varchar](100) NOT NULL)
   

And I have created a sample stored procedure to do the same.

ALTER PROCEDURE [InsertCustomerInfo]
    @CustomerJson NVarchar(MAX) 
AS
BEGIN
      
        INSERT INTO CustomerDetails (CustomerUniqueGUID,CustomerName)
        SELECT Customer.customerID, Fields.fieldValue FROM 
        OPENJSON(@CustomerJson)
                        WITH ([customerdata] nvarchar(max) as json
                        ) AS CustomerBatch
                        cross apply openjson (CustomerBatch.[customerdata])
                        with
                        (
                            customerID varchar(100),
                            customerDetails nvarchar(max) as json
                        ) AS Customer
                        cross apply openjson (Customer.customerDetails) with
                        (   
                            fieldId nvarchar(100),
                            fieldValue nvarchar(max)
                        ) as Fields
                        WHERE Fields.fieldID='101'              
        AND CustomerDetails.CustomerUniqueGUID not in (SELECT CustomerUniqueGUID FROM CustomerDetails WHERE ISActive=1 )

                
        If @@ROWCOUNT > 0               
        BEGIN
            INSERT INTO [CustomerFieldData](CustomerID,FieldID,FieldValue) SELECT L.CustomerID,Fields.fieldId,Fields.fieldValue FROM    
            CustomerDetails L INNER JOIN  
            OPENJSON(@CustomerJson)
            WITH ([customerdata] nvarchar(max) as json
            ) AS CustomerBatch
            cross apply openjson (CustomerBatch.[customerdata])
            with
            (   customerID varchar(100),
                customerDetails nvarchar(max) as json
            ) AS Customer
            cross apply openjson (Customer.customerDetails) with
            (   
                fieldId nvarchar(100),
                fieldValue nvarchar(max)
            ) as Fields
            ON L.CustomerUniqueGUID=Customers.customerID
                                        

        END  
    
END

But data to CustomerFieldData is not happening properly and it is taking more time. Is there any issue with my script? please help.

Answer 1

You can use an INSERT with an OUTPUT here to get the value of both your new ID and the GUID:

DECLARE @JSON nvarchar(MAX) = N'{
  "customerdata": [
    {
      "customerID": "abcsd112-1234-4c01-abcd-bb2fb084dc52",
      "customerDetails": [
        {
          "fieldId": "100",
          "fieldValue": "ABC"
        },
        {
          "fieldId": "101",
          "fieldValue": "TESTCUSTOMER001"
        },
        {
          "fieldId": "102",
          "fieldValue": "1000"
        },
        {
          "fieldId": "103",
          "fieldValue": "TESTNAME"
        }     
            
      ]
    },
    {
      "customerID": "cdfsd112-4c01-45d7-abcd-9c9662d4ca30",
      "customerDetails": [
        {
          "fieldId": "100",
          "fieldValue": "CDE"
        },
        {
          "fieldId": "101",
          "fieldValue": "TESTCUSTOMER002"
        },
        {
          "fieldId": "102",
          "fieldValue": "1002"
        },
        {
          "fieldId": "103",
          "fieldValue": "TESTNAME2"
        }    
      ]
    }    
  ]
}';

CREATE TABLE #CustomerIDs (CustomerID bigint,
                           CustomerUniqueGUID varchar(100)) ;

INSERT INTO dbo.CustomerDetails (CustomerUniqueGUID,CustomerName)
OUTPUT inserted.CustomerID, inserted.CustomerUniqueGUID
INTO #CustomerIDs
SELECT OJ.customerID,
       CD.fieldValue
FROM OPENJSON(@JSON,'$.customerdata')
     WITH(customerID varchar(100), 
          customerDetails nvarchar(MAX) AS JSON) OJ
     CROSS APPLY OPENJSON(OJ.customerDetails)
                 WITH(fieldId int,
                      fieldValue varchar(100)) CD
WHERE CD.fieldID = 101;


INSERT INTO dbo.CustomerFieldData (CustomerID,FieldID,FieldValue)
SELECT CI.CustomerID,
       CD.fieldID,
       CD.fieldValue
FROM OPENJSON(@JSON,'$.customerdata')
     WITH (customerID varchar(100), --Let's use the right data type again
           customerDetails nvarchar(MAX) AS JSON) OJ
     CROSS APPLY OPENJSON(OJ.customerDetails)
                 WITH(fieldId int,
                      fieldValue varchar(100)) CD
     JOIN #CustomerIDs CI ON OJ.customerID = CI.CustomerUniqueGUID



DROP TABLE #CustomerIDs;
GO
SELECT *
FROM dbo.CustomerDetails;

SELECT *
FROM dbo.CustomerFieldData;