Json - Remove Element from Array by a Condition using SQL

Question

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": []
    }]
}

What is your SQL Server version? Deleting an item from JSON array is not possible with JSON_MODIFY(), so you'll need a different approach. — Zhorov
– Zhorov, Commented Jul 24, 2020 at 20:20

Zhorov · Accepted Answer · 2020-07-24 20:35:53Z

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 table
JSON_VALUE() for the appropriate WHERE clause
STRING_AGG() and FOR JSON to build the final JSON

Fixed 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": []
}]
}

Collectives™ on Stack Overflow

Json - Remove Element from Array by a Condition using SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related