How to Update and Insert Array of Objects in table with OPENJSON and where conditions

Question

I want to update and insert the stock, InvM, and Invoice table with OPENJSON(). I am new at OPENJSON() in SQL Server. I have an array of objects and I want to insert each object to new row of the tables. I want to iterate through every object and insert or update it using Where clause and 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,
}
]'

How to update the Stock table and reduce the quantity with where condition if the Name of the medicine in the object array matches with the medicine in the Stock table (I came up with this but it is not working correctly):

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

Same goes for the InvM and Invoice table I want to insert new row with where condition

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

The input is not a valid JSON, so OPENJSON() will return an error. — Zhorov
– Zhorov, Commented Apr 19, 2022 at 10:57
@Zhorov can you show me the correct way to update and insert with OPENJSON and where clause — Syed Muhammad Moiz
– Syed Muhammad Moiz, Commented Apr 19, 2022 at 11:05
Step 1 would be having valid JSON, @SyedMuhammadMoiz . Until then, you can't parse it as JSON, because it isn't (valid) JSON. — Thom A
– Thom A ♦, Commented Apr 19, 2022 at 11:09

Zhorov · Accepted Answer · 2022-04-19 13:11:28Z

3

You need to parse the input JSON with OPENJSON() and update the table using an appropriate JOIN. The following example is a posiible solution to your problem:

Sample data:

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 statement:

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

Result:

Name	Quantity
PENZOL TAB 40 MG	98
Calpax_D Syp 120Ml	99

edited Apr 19, 2022 at 13:11

answered Apr 19, 2022 at 12:29

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

3 Comments

Syed Muhammad Moiz Over a year ago

Thanks this helped alot can u also tell me how to use where clause properly in insert and OPENJSON

Syed Muhammad Moiz Over a year ago

insert into Invoice (SNO, RNDT, Invno, Code, ...) select SNO, RNDT, InvNo, Code, .... from OPENJSON(@files) with ( Batch INT '$.Batch', Code INT '$.Code', ....... )where Stock.Code = Code;

Zhorov Over a year ago

@SyedMuhammadMoiz, the INSERT statement is correct, I'm probably missing something about the WHERE clause. Can you update the question with additional explanations? Thanks.

Collectives™ on Stack Overflow

How to Update and Insert Array of Objects in table with OPENJSON and where conditions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related