Insert parameter values and JSON string values

Question

I tried to create a stored procedure to insert data by JSON and Parameter

Declare @PaymentJson        Nvarchar(1000)  = N'{"type":4},{"type":1`},{"type":2},{"type":3}'  
        ,@ProductID         bigint  = 5
        ,@UserCode          bigint  = 2

Insert into PaymentType ([ProductID], [PaymentType],[UserCode])
            SELECT @ProductID,
                    PaymentType = MAX(CASE WHEN LOWER([key]) = 'type' THEN [value] END),
                    @UserCode
            FROM OPENJSON(@PaymentJson)

Result is not true, is 1 first JSON (in this case type is 4) row.

(1 row affected)

I need, the required number of JSON strings , insert into row PaymentType in this case should be:

(4 row affected)

ProductID   PaymentType UserCode
5           4           2
5           1           2
5           2           2
5           3           2

My DBMS is SQL Server 2019

Zhorov · Accepted Answer · 2020-04-22 07:42:01Z

4

The reason for this unexpected behaviour is the fact, that the input JSON is not valid (multiple root elements), but OPENJSON() parses successfully the first object from this invalid JSON (although ISJSON() returns 0). As a workaround you need to transform the input JSON into a valid JSON array and parse it with OPENJSON() and explicit schema with the appropriate column definition:

DECLARE 
   @PaymentJson nvarchar(1000) = N'{"type":4},{"type":1},{"type":2},{"type":3}',
   @ProductID bigint = 5,
   @UserCode bigint = 2

INSERT INTO PaymentType ([ProductID], [PaymentType], [UserCode])
SELECT 
   @ProductID,
   [Type],
   @UserCode
FROM OPENJSON(CONCAT('[', @PaymentJson, ']')) WITH ([Type] int '$.type')

edited Apr 22, 2020 at 7:42

answered Apr 22, 2020 at 7:36

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amirhossein Over a year ago

Thanks for your detailed answer , its worked correctly .

Muhammad Vakili · Accepted Answer · 2020-04-22 07:38:16Z

1

First of all, your JSON string is not valid it must be an array

N'[{"type":4},{"type":1},{"type":2},{"type":3}]'

second, you don't need to use an aggregate function in the select statement

so your code should be like this:

Declare @PaymentJson        Nvarchar(1000)  = N'[{"type":4},{"type":1},{"type":2},{"type":3}]'  
        ,@ProductID         bigint  = 5
        ,@UserCode          bigint  = 2

Insert into PaymentType ([ProductID], [PaymentType],[UserCode])
            SELECT @ProductID,
                    PaymentType = (CASE WHEN LOWER([key]) = 'type' THEN [value] END),
                    @UserCode
            FROM OPENJSON(@PaymentJson)

answered Apr 22, 2020 at 7:38

Muhammad Vakili

7085 silver badges19 bronze badges

1 Comment

Amirhossein Over a year ago

Hi, i try it , i have a error : Cannot insert the value NULL into column 'PaymentType', table 'PaymentType'; column does not allow nulls. INSERT fails. then commented insert and use select, its tru, return null

Collectives™ on Stack Overflow

Insert parameter values and JSON string values

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related