Last active
March 10, 2020 18:43
-
-
Save bertwagner/4d3f3b91b5771e26779a2f53f0a683bc to your computer and use it in GitHub Desktop.
SQL Server 2016 JSON's MODIFY_JSON Delete Array Element
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @garage nvarchar(1000) = N'{ "Cars": [{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GLI" }, "Year": 2003, "SellDate" : "2017-02-17T00:00:00.000Z" }, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] }' | |
-- I realize it's not worth keeping the original Volkswagen in my @garage data any longer, so let's completely remove it. | |
-- Note, if we use NULL as per the MSDN documentation, we don't actually remove the first car element of the array - it just gets replaced with NULL | |
-- This is problematic if we expect the indexes of our array to shift by -1. | |
SELECT JSON_MODIFY(@garage, '$.Cars[0]', NULL) | |
-- Output: { "Cars": [null, { "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] } | |
-- To truly delete it (and not have the NULL appear as the first item in the array) we have to convert to a rowset, select everything that's not the first row, aggregate the rows into a string (UGH) and then recreate as JSON. | |
-- This is incredibly ugly. The STREAM_AGG() function in SQL vNext should make it a little cleaner, but why doesn't the JSON_MODIFY NULL syntax just get rid of the element in the array? | |
-- I have opened a Microsoft connect issue for this here: https://connect.microsoft.com/SQLServer/feedback/details/3120404 | |
SELECT JSON_QUERY('{ "Cars" : [' + | |
STUFF(( | |
SELECT ',' + value | |
FROM OPENJSON(@garage, '$.Cars') | |
WHERE [key] <> 0 | |
FOR XML PATH('')), 1, 1, '') + '] }') | |
-- Output: { "Cars" : [{ "Make": "Subaru", "Model": { "Base": "Impreza", "Trim": "Premium" }, "Year": 2016, "PurchaseDate": "2015-08-18T00:00:00.000Z" },{ "Make": "Volkswagen", "Model": { "Base": "Golf", "Trim": "GTI" }, "Year": 2017, "PurchaseDate": "2017-02-19T00:00:00.000Z" }] } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment