如何使用OPENJSON和where条件更新和插入表中的对象数组

Question

我想用OPENJSON()更新并插入stock、InvM和Invoice表。我是新的在OPENJSON()在SQL服务器。我有一个对象数组，我想把每个对象插入到表的新行中。我想遍历每个对象，并使用Where子句和OPENJSON()插入或更新它:

Array of Objects:

DECLARE @f NVARCHAR(MAX) = N'[{
"Batch": "CP008",
"Bonus": -26,
"Code": 002,
"Cost": 50,
"Disc1": 0,
"Name": "Calpax_D Syp 120Ml",
"Price": undefined,
"Quantity": "1",
"SNO": 9,
"STP": 153,
"Stax": 0,
"TP": 50,
"Total": 50,
"invoiceno": 71,
"profit": 156,
"randomnumber": "3MO0FMDLUX0D9P1N7HGV",
"selected": false,
},
{
"Batch": "P009",
"Bonus": 0,
"Code": 823,
"Cost": 237.14999389648438,
"Disc1": 0,
"Name": "PENZOL TAB 40 MG",
"Price": undefined,
"Quantity": "2",
"SNO": 94,
"STP": 263.5,
"Stax": 0,
"TP": 263.5,
"Total": 527,
"invoiceno": 71,
"profit": 156,
"randomnumber": "3MO0FMDLUX0D9P1N7HGV",
"selected": false,
}
]'

如果对象数组中的药物名称与Stock表中的药物匹配，如何更新Stock表并减少where条件的数量(我想到了这个，但它不能正常工作):

UPDATE Stock 
SET Qty = Qty - qty  
from OPENJSON(@files) 
with(qty INT '$.Quantity', Name12 VARCHAR(55) '$.Name') 
where Stock.Name = Name12  

同样适用于InvM和Invoice表，我想插入where条件的新行

insert into InvM (RNDT, Dat, SMID, CID, Total, USR, RefNo, SRID, Txt,InvTime) 
select RNDT, getdate(), Salesman.SMID, Customer.CID,@total, USR.Name, 0, 
0,Salesman.Name,CURRENT_TIMESTAMP
from Salesman, USR,Customer, OPENJSON(@files)  
with(
RNDT NVARCHAR(max) '$.randomnumber'
)
where USR.Name = 'moiz'

insert into Invoice (SNO, RNDT, Invno, Code, Name, Batch, STP, Qty, Bon, Disc, Stax, NET, 
TP, Cost, Profit)
select SNO, RNDT, InvNo, Code, Name, Batch, STP, Qty, Bon, Disc, Stax, NET, TP, 
Cost,profit  
from OPENJSON(@files) 
with (  
Batch INT '$.Batch',
Bon INT '$.Bouns',
Code INT '$.Code',
Cost INT '$.Cost',
Disc INT '$.Disc1',
Name NVARCHAR(Max) '$.Name',
STP INT '$.STP',
Qty INT '$.Quantity',
SNO INT '$.SNO',
Stax INT '$.Stax',
RNDT NVARCHAR(max) '$.randomnumber',
InvNo INT '$.invoiceno',
TP INT '$.TP',
NET INT '$.Total',
profit INT '$.profit'
)

Answer 1

您需要使用OPENJSON()解析输入JSON，并使用适当的JOIN更新表。下面的例子是一个可能的解决方案:

样本数据:

SELECT *
INTO Stock
FROM (VALUES
('PENZOL TAB 40 MG', 100),
('Calpax_D Syp 120Ml', 100)
) v (Name, Quantity)

JSON:

DECLARE @files NVARCHAR(MAX) = N'[
{
"Batch":"CP008",
"Bonus":-26,
"Code":2,
"Cost":50,
"Disc1":0,
"Name":"Calpax_D Syp 120Ml",
"Price":"undefined",
"Quantity":"1",
"SNO":9,
"STP":153,
"Stax":0,
"TP":50,
"Total":50,
"invoiceno":71,
"profit":156,
"randomnumber":"3MO0FMDLUX0D9P1N7HGV",
"selected":false
},
{
"Batch":"P009",
"Bonus":0,
"Code":823,
"Cost":237.14999389648438,
"Disc1":0,
"Name":"PENZOL TAB 40 MG",
"Price":"undefined",
"Quantity":"2",
"SNO":94,
"STP":263.5,
"Stax":0,
"TP":263.5,
"Total":527,
"invoiceno":71,
"profit":156,
"randomnumber":"3MO0FMDLUX0D9P1N7HGV",
"selected":false
}
]';

UPDATE语句:

UPDATE s
SET s.Quantity = s.Quantity - j.Quantity
FROM Stock s
JOIN OPENJSON(@files) WITH (
Name varchar(100) '$.Name', 
Quantity int '$.Quantity' 
) j ON s.Name = j.Name

结果:

<表类>名称数量tbody><<tr>PENZOL标签40毫克98Calpax_D Syp 120Ml99