Stack Overflow Asked by redoc01 on January 3, 2022
I need to use SQL in SQL Server to delete and element or array if the p_Num = aValue.
So if aValue = '12fab35c2b3d4203bf8a252015b862af' I need to remove that element from pProds from that array?.
How can this be done in Json_Modify, I will try as soon as I have posted this question to try and use Json_Modify because I have not used it a lot.
{
"pType": "1",
"pTitle": "Plunge Neck Flared Sleeve Skater Dress | Boohoo",
"pProds": [{
"formM": 1,
"sDesc": "<p><a data-ved=0CA0QjhxqFwoTCKCz-Jq13uoCFQAAAAAdAAAAABBq rel=noopener target=_blank href=https://ca.boohoo.com/plunge-neck-flared-sleeve-skater-dress/DZZ17448-104-22.html jsaction=focus:kvVbVb; mousedown:kvVbVb; touchstart:kvVbVb; class=Beeb4e style=-webkit-tap-highlight-color: transparent; text-decoration-line: underline; color: rgb(241, 243, 244); display: -webkit-box; font-size: 20px; line-height: 30px; max-height: 60px; overflow: hidden; text-overflow: ellipsis; overflow-wrap: break-word; -webkit-line-clamp: 2; -webkit-box-orient: vertical; font-family: Roboto, HelveticaNeue, Arial, sans-serif; background-color: rgb(20, 21, 24);>Plunge Neck Flared Sleeve Skater Dress | Boohoo</a></p>",
"lDesc": "<p><a data-ved=0CA0QjhxqFwoTCKCz-Jq13uoCFQAAAAAdAAAAABBq rel=noopener target=_blank href=https://ca.boohoo.com/plunge-neck-flared-sleeve-skater-dress/DZZ17448-104-22.html jsaction=focus:kvVbVb; mousedown:kvVbVb; touchstart:kvVbVb; class=Beeb4e style=-webkit-tap-highlight-color: transparent; text-decoration-line: underline; color: rgb(241, 243, 244); display: -webkit-box; font-size: 20px; line-height: 30px; max-height: 60px; overflow: hidden; text-overflow: ellipsis; overflow-wrap: break-word; -webkit-line-clamp: 2; -webkit-box-orient: vertical; font-family: Roboto, HelveticaNeue, Arial, sans-serif; background-color: rgb(20, 21, 24);>Plunge Neck Flared Sleeve Skater Dress | Boohoo</a></p>",
"pColor": "#000000",
"pSize": null,
"postage": "20",
"quatity": 8,
"aPrice": "10",
"rPrice": "45.99",
"Discounted": "",
"Price": "45.99",
"p_Num": "12fab35c2b3d4203bf8a252015b862af",
"images": [{
"mN": 1,
"idImage": "image1",
"fileName": "9396099031fc402f96221d01472e6861.jpg",
"bytes": "/img/ProdImages/12fab35c2b3d4203bf8a252015b862af/9396099031fc402f96221d01472e6861.jpg"
}]
},
{
"formM": 2,
"sDesc": "<p><a data-ved=0CA0QjhxqFwoTCKCz-Jq13uoCFQAAAAAdAAAAABBq rel=noopener target=_blank href=https://ca.boohoo.com/plunge-neck-flared-sleeve-skater-dress/DZZ17448-104-22.html jsaction=focus:kvVbVb; mousedown:kvVbVb; touchstart:kvVbVb; class=Beeb4e style=-webkit-tap-highlight-color: transparent; text-decoration-line: underline; color: rgb(241, 243, 244); display: -webkit-box; font-size: 20px; line-height: 30px; max-height: 60px; overflow: hidden; text-overflow: ellipsis; overflow-wrap: break-word; -webkit-line-clamp: 2; -webkit-box-orient: vertical; font-family: Roboto, HelveticaNeue, Arial, sans-serif; background-color: rgb(20, 21, 24);>Plunge Neck Flared Sleeve Skater Dress | Boohoo</a></p>",
"lDesc": "<p><a data-ved=0CA0QjhxqFwoTCKCz-Jq13uoCFQAAAAAdAAAAABBq rel=noopener target=_blank href=https://ca.boohoo.com/plunge-neck-flared-sleeve-skater-dress/DZZ17448-104-22.html jsaction=focus:kvVbVb; mousedown:kvVbVb; touchstart:kvVbVb; class=Beeb4e style=-webkit-tap-highlight-color: transparent; text-decoration-line: underline; color: rgb(241, 243, 244); display: -webkit-box; font-size: 20px; line-height: 30px; max-height: 60px; overflow: hidden; text-overflow: ellipsis; overflow-wrap: break-word; -webkit-line-clamp: 2; -webkit-box-orient: vertical; font-family: Roboto, HelveticaNeue, Arial, sans-serif; background-color: rgb(20, 21, 24);>Plunge Neck Flared Sleeve Skater Dress | Boohoo</a></p>",
"pColor": "#000000",
"pSize": null,
"postage": "20",
"quatity": 8,
"aPrice": "10",
"rPrice": "45.99",
"Discounted": "",
"Price": "45.99",
"p_Num": "1gf74g5575hfbgbgugu5u5",
"images": [{
"mN": 1,
"idImage": "image1",
"fileName": "9396099031fc402f96221d01472e6861.jpg",
"bytes": "/img/ProdImages/12fab35c2b3d4203bf8a252015b862af/9396099031fc402f96221d01472e6861.jpg"
}]
},
{
"formM": 0,
"sDesc": "",
"lDesc": "",
"pColor": "",
"pSize": "0",
"postage": "0",
"quatity": 0,
"aPrice": "0",
"rPrice": "0",
"Discounted": "0",
"Price": "0",
"p_Num": "c402d56ef08847ed94298733365f22c7",
"images": []
}]
}
I don't think that you can delete an item from JSON array using JSON_MODIFY(), currently JSON_MODIFY( only supports append modifier. So, you need to use a combination of string manipulations and JSON functions:
OPENJSON() to parse the input JSON as tableJSON_VALUE() for the appropriate WHERE clauseSTRING_AGG() and FOR JSON to build the final JSONFixed JSON (the JSON from the question has errors):
DECLARE @json nvarchar(max) = N'{
"pType": "1",
"pTitle": "Plunge Neck Flared Sleeve Skater Dress | Boohoo",
"pProds": [{
"formM": 1,
"sDesc": "rwgwgwgwgwrgwgwg",
"lDesc": "rgwgwrgwrgwrg",
"pColor": "#000000",
"pSize": null,
"postage": "20",
"quatity": 8,
"aPrice": "10",
"rPrice": "45.99",
"Discounted": "",
"Price": "45.99",
"p_Num": "12fab35c2b3d4203bf8a252015b862af",
"images": [{
"mN": 1,
"idImage": "image1",
"fileName": "9396099031fc402f96221d01472e6861.jpg",
"bytes": "/img/ProdImages/12fab35c2b3d4203bf8a252015b862af/9396099031fc402f96221d01472e6861.jpg"
}]
},{
"formM": 2,
"sDesc": "gwrgwrgwgwg",
"lDesc": "wrgwrgwrgwrgwrg",
"pColor": "#000000",
"pSize": null,
"postage": "20",
"quatity": 8,
"aPrice": "10",
"rPrice": "45.99",
"Discounted": "",
"Price": "45.99",
"p_Num": "1gf74g5575hfbgbgugu5u5",
"images": [{
"mN": 1,
"idImage": "image1",
"fileName": "9396099031fc402f96221d01472e6861.jpg",
"bytes": "/img/ProdImages/12fab35c2b3d4203bf8a252015b862af/9396099031fc402f96221d01472e6861.jpg"
}]
}, {
"formM": 0,
"sDesc": "",
"lDesc": "",
"pColor": "",
"pSize": "0",
"postage": "0",
"quatity": 0,
"aPrice": "0",
"rPrice": "0",
"Discounted": "0",
"Price": "0",
"p_Num": "c402d56ef08847ed94298733365f22c7",
"images": []
}]
}'
Statement:
SELECT j.pType, j.pTitle, JSON_QUERY(c.pProds) AS pProds
FROM OPENJSON(@json) WITH (
pType varchar(10) '$.pType',
pTitle varchar(100) '$.pTitle',
pProds nvarchar(max) '$.pProds' AS JSON
) j
CROSS APPLY (
SELECT CONCAT('[', STRING_AGG([value], ','), ']') AS pProds
FROM OPENJSON(j.pProds)
WHERE JSON_VALUE([value], '$.p_Num') <> '12fab35c2b3d4203bf8a252015b862af'
) c
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
Result:
{
"pType":"1",
"pTitle":"Plunge Neck Flared Sleeve Skater Dress | Boohoo",
"pProds":[{
"formM": 2,
"sDesc": "gwrgwrgwgwg",
"lDesc": "wrgwrgwrgwrgwrg",
"pColor": "#000000",
"pSize": null,
"postage": "20",
"quatity": 8,
"aPrice": "10",
"rPrice": "45.99",
"Discounted": "",
"Price": "45.99",
"p_Num": "1gf74g5575hfbgbgugu5u5",
"images": [{
"mN": 1,
"idImage": "image1",
"fileName": "9396099031fc402f96221d01472e6861.jpg",
"bytes": "/img/ProdImages/12fab35c2b3d4203bf8a252015b862af/9396099031fc402f96221d01472e6861.jpg"
}]
},{
"formM": 0,
"sDesc": "",
"lDesc": "",
"pColor": "",
"pSize": "0",
"postage": "0",
"quatity": 0,
"aPrice": "0",
"rPrice": "0",
"Discounted": "0",
"Price": "0",
"p_Num": "c402d56ef08847ed94298733365f22c7",
"images": []
}]
}
Answered by Zhorov on January 3, 2022
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP