Insert JSON Array Request to Database

dotNET
August 28, 2023
204 views
0 votes
2 Answers

I am facing issue with inserting the JSON array into tables

Doc Table : Id, Name, Desc, RefName, IsActive

Con Table : Id, Field, Criter, DId, Conjunction, IsActive

We map doc table Id Column to DId column

This is sample JSON

    {
    "SubDoc" : [{ "Id": 0,  "Name": "file1", "Desc": "stockvalue", "RefName": "", "IsActive": "true", "Conditions": [{ "Id": 0, "Field": 1, "Criter": 0.00, "DId": 0, "Conjuction": "AND", "IsActive": "true" },{ "Id": 0, "Field": 1, "Criter": 0.00, "DIdId": 0, "Conjuction": "AND", "IsActive": "true"}]}, { "Id": 0,  "Name": "file1", "Desc": "", "RefName": "", "IsActive": "true", "Conditions": [{ "Id": 0, "Field": 1, "Criter": 0.00, "DIdId": 0, "Conjuction": "AND", "IsActive": "true" },{ "Id": 0, "Field": 1, "Criter": 0.00, "DIdId": 0, "Conjuction": "AND", "IsActive": "true"}]
    }]}

ALTER PROCEDURE [dbo].[SaveDoc] 
    @DocRequest NVARCHAR(MAX)
AS
BEGIN   
    SET NOCOUNT ON;

        DECLARE @Conditions nvarchar(max)
        DECLARE @SubDoc nvarchar(max)
        DECLARE @Id int
        DECLARE @index INT = 0
        DECLARE @count INT          
        
        SELECT @count = COUNT(*) FROM OPENJSON(@DocRequest,'$.SubDoc');     
        PRINT 'count'+ CAST(@count AS NVARCHAR(MAX))
        
        BEGIN TRANSACTION
                           BEGIN TRY
                                 BEGIN
                                 PRINT '0'
                                 WHILE @index < @count
                                 BEGIN
                                         SELECT * INTO #SubJsonRequest
                                         FROM OPENJSON (@DocRequest, '$.SubDoc[' + CAST(@index AS NVARCHAR(MAX)) + ']')
                                     WITH (
                                              [Id] int '$.Id',
                                                                                          [Name] [nvarchar](150) '$.Name',
                                              [Desc] [nvarchar](max) '$.Desc',
                                              [RefName] [nvarchar](200) '$.RefName',
                                              [SourceLink] [nvarchar](200) '$.SourceLink',
                                              [IsActive] [bit] '$.IsActive',                                              
                                              Conditions NVARCHAR(MAX) '$.Conditions'AS JSON
                                          )
                                        Select @Conditions = [Conditions] from #SubJsonRequest
                                         INSERT INTO [dbo].[Document]([Name],[Desc],[RefName] ,[SourceLink],[IsActive])
                                         (SELECT [Name],[Desc],[RefName] ,[SourceLink],[IsActive] FROM #DocRequest)
                                         SELECT SCOPE_IDENTITY() AS Id
                                     END
                                     set @Id = SCOPE_IDENTITY()
                                     PRINT @Conditions
                                     PRINT @DocumentId
                                     EXEC [dbo].SaveCondition @Conditions,@Id
                                     DROP TABLE #SubJsonRequest
                                    Set @index = @index + 1
                                  PRINT @index
                                 END
                                 
                                 PRINT '1'
                                 COMMIT TRANSACTION
                                  
                           END TRY
                           BEGIN CATCH
                             IF (@@TRANCOUNT > 0)                       
                             DECLARE @Message varchar(MAX) = CONCAT('ERROR Line: ', CAST(ERROR_LINE() AS nvarchar(10)), 'Message: '+ ERROR_MESSAGE())
                             RAISERROR(@Message,16,1)
                             ROLLBACK;
                          END CATCH
END

When I am run the Stored Procedure it shows below Error Message:

Invalid object name ‘#SubJsonRequest’.

As per my knowledge while loop is not working properly. Can someone help me how to loop the array value and insert to tables

Answers

Chosen as BEST ANSWER

ALTER PROCEDURE [dbo].[SaveDoc] @DocRequest NVARCHAR(MAX) AS BEGIN
SET NOCOUNT ON;

    DECLARE @Conditions nvarchar(max)
    DECLARE @SubDoc nvarchar(max)
    DECLARE @Id int
    DECLARE @index INT = 0
    DECLARE @count INT          
    
    SELECT @count = COUNT(*) FROM OPENJSON(@DocRequest,'$.SubDoc');     
    PRINT 'count'+ CAST(@count AS NVARCHAR(MAX))
    
    BEGIN TRANSACTION
                       BEGIN TRY
                             BEGIN
                             
                             WHILE @index < @count
                             BEGIN
                             IF OBJECT_ID('tempdb..#SubJsonRequest') IS NOT NULL 
                           BEGIN
                             DROP TABLE #SubJsonRequest
                           END
                                     SELECT * INTO #SubJsonRequest
                                     FROM OPENJSON (@DocRequest, '$.SubDoc[' + CAST(@index AS NVARCHAR(MAX)) + ']')
                                 WITH (
                                          [Id] int '$.Id',
                                                                                      [Name] [nvarchar](150) '$.Name',
                                          [Desc] [nvarchar](max) '$.Desc',
                                          [RefName] [nvarchar](200) '$.RefName',
                                          [SourceLink] [nvarchar](200) '$.SourceLink',
                                          [IsActive] [bit] '$.IsActive',                                              
                                          Conditions NVARCHAR(MAX) '$.Conditions'AS JSON
                                      )
                                    Select @Conditions = [Conditions] from #SubJsonRequest
                                    Set @index = @index + 1
                                     INSERT INTO [dbo].[Document]([Name],[Desc],[RefName] ,[SourceLink],[IsActive])
                                     (SELECT [Name],[Desc],[RefName] ,[SourceLink],[IsActive] FROM #DocRequest)
                                     SELECT SCOPE_IDENTITY() AS Id
                                 END
                                 set @Id = SCOPE_IDENTITY()
                                
                                 EXEC [dbo].SaveCondition @Conditions,@Id
                                 DROP TABLE #SubJsonRequest
                                
                              PRINT @index
                             END
                             
                             PRINT '1'
                             COMMIT TRANSACTION
                              
                       END TRY
                       BEGIN CATCH
                         IF (@@TRANCOUNT > 0)                       
                         DECLARE @Message varchar(MAX) = CONCAT('ERROR Line: ', CAST(ERROR_LINE() AS nvarchar(10)), 'Message: '+ ERROR_MESSAGE())
                         RAISERROR(@Message,16,1)
                         ROLLBACK;
                      END CATCH

END

(Edit)

First, handling your ETL in TSQL is likely not your best option. There are other tools and services available that you could transform the data before attempting to insert it into your database.

This is not a complete script, you’re not only calling out to other undefined objects, but there appears to be some code missing as well. You haven’t provided the DDL for the tables you are using either.

SCOPE_IDENTITY() has documented issues.

We typically look for set based options to handle data in TSQL/SQL Server, rather than loops. CURSOR and WHILE are both code smells.

You may also want to consider avoiding key words in your object names (such as KEY and DESC). While they can be escaped and used successfully they are simply just harder to deal with, and honestly quite annoying.

Having said all of that, consider the following:

DECLARE @Documents TABLE (RowID BIGINT IDENTITY, DocumentID INT, Name NVARCHAR(50), [Desc] NVARCHAR(50), RefName NVARCHAR(50), IsActive VARCHAR(5), Conditions NVARCHAR(MAX));
DECLARE @DocumentConditions TABLE (DocumentID INT, [key] INT, Id INT, Field INT, Criter DECIMAL(5,2), Conjuction VARCHAR(10), IsActive VARCHAR(5));
/* Temporary table objects to demonstrate the insert */

DECLARE @DocRequest NVARCHAR(MAX) = '{
"SubDoc" : [{ "Id": 0,  "Name": "file1", "Desc": "stockvalue", "RefName": "", "IsActive": "true", "Conditions": [{ "Id": 0, "Field": 1, "Criter": 0.00, "Id": 0, "Conjuction": "AND", "IsActive": "true" },{ "Id": 0, "Field": 1, "Criter": 0.00, "Id": 0, "Conjuction": "AND", "IsActive": "true"}]}, { "Id": 0,  "Name": "file1", "Desc": "", "RefName": "", "IsActive": "true", "Conditions": [{ "Id": 0, "Field": 1, "Criter": 0.00, "Id": 0, "Conjuction": "AND", "IsActive": "true" },{ "Id": 0, "Field": 1, "Criter": 0.00, "Id": 0, "Conjuction": "AND", "IsActive": "true"}]
}]}';
DECLARE @Output TABLE (RowID BIGINT, Conditions NVARCHAR(MAX));
/* A table variable to hold the output from the inital insert */

;WITH Numbers AS (
SELECT 0 AS Number , COUNT(*) AS MaxNumber
  FROM OPENJSON(@DocRequest,'$.SubDoc')
UNION ALL
SELECT Number+1, MaxNumber
  FROM Numbers 
 WHERE Number < MaxNumber
)

INSERT INTO @Documents (DocumentID, Name, [Desc], RefName, IsActive, Conditions) 
OUTPUT INSERTED.RowID, INSERTED.Conditions INTO @Output
SELECT Id, Name, [Desc], RefName, IsActive, Conditions
  FROM Numbers
    CROSS APPLY (SELECT [key], value FROM OPENJSON(@DocRequest,'$.SubDoc['+CAST(Number AS NVARCHAR(MAX))+']')) a
    PIVOT (
           MAX(value) FOR [key] IN (Id, Name, [Desc], RefName, IsActive, Conditions)
          ) p

INSERT INTO @DocumentConditions (DocumentID, [key], Id, Field, Criter, Conjuction, IsActive)
SELECT p.RowID, p.[key], p.Id, p.Field, p.Criter, p.Conjuction, p.IsActive
  FROM @Output o
    CROSS APPLY (SELECT [key], value FROM OPENJSON(Conditions,'$') ) x
    CROSS APPLY (SELECT [key], value FROM OPENJSON(value, '$')     ) c
    PIVOT (
           MAX(c.value) FOR c.[key] IN (Id, Field, Criter, Conjuction, IsActive)
          ) p

SELECT *
  FROM @Documents

SELECT *
  FROM @DocumentConditions

@Documents

RowID	DocumentID	Name	Desc	RefName	IsActive	Conditions
1	0	file1	stockvalue		true	[{ "Id": 0, "Field": 1, "Criter": 0.00, "Id": 0, "Conjuction": "AND", "IsActive": "true" },{ "Id": 0, "Field": 1, "Criter": 0.00, "Id": 0, "Conjuction": "AND", "IsActive": "true"}]
2	0	file1			true	[{ "Id": 0, "Field": 1, "Criter": 0.00, "Id": 0, "Conjuction": "AND", "IsActive": "true" },{ "Id": 0, "Field": 1, "Criter": 0.00, "Id": 0, "Conjuction": "AND", "IsActive": "true"}]

@DocumentConditions

DocumentID	key	Field	Conjuction	IsActive
1	0	1	AND	true
1	1	1	AND	true
2	0	1	AND	true
2	1	1	AND	true

Please signup or login to give your own answer.

Click here to cancel reply.