sql - 插入参数值和 JSON 字符串值

Question

我尝试创建一个存储过程来通过JSON和参数插入数据

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)

结果不正确，是第 1 个 JSON(在本例中类型为 4)行。

(1 row affected)

我需要，在本例中插入行 PaymentType 所需的 JSON 字符串数量应该是:

(4 row affected)

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

我的 DBMS 是 SQL Server 2019

Answer 1

出现这种意外行为的原因是，输入 JSON 无效(多个根元素)，但 OPENJSON() 成功解析了此无效 JSON 中的第一个对象(尽管 ISJSON() 返回 0)。作为解决方法，您需要将输入 JSON 转换为有效的 JSON 数组，并使用 OPENJSON() 和具有适当列定义的显式架构来解析它:

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')