Remove element from json array by condition sql server 2016

Question

I have the following json data

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'{  
        "FirstName":"Jignesh",  
        "LastName":"Trivedi",  
        "Code":"CCEEDD",  
        "Addresses":[  
            { "Address":"Test 0", "City":"Gandhinagar", "State":"Gujarat"},  
            { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"}  
        ]  
    }'

I need to remove element from an array with a condition (using json_modify of other sql server 2016 functions), for example I want to delete address which value is "Test 1". Can anybody help?

Answer 1

Solution:

JSON_MODIFY() doesn't support delete option in path argument, so one possible approach here is to empty "Addresses" JSON array and then fill this array with matching items. For SQL Server 2016+ you may try to generate and execute a dynamic statement. If you use SQL Server 2017+, you may pass a variable as path argument.

Example for SQL Server 2016+:

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'{  
   "FirstName":"Jignesh",  
   "LastName":"Trivedi",  
   "Code":"CCEEDD",  
   "Addresses":[  
       { "Address":"Test 0", "City":"Gandhinagar", "State":"Gujarat"},  
       { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"},  
       { "Address":"Test 2", "City":"Gandhinagar", "State":"Gujarat"},  
       { "Address":"Test 3", "City":"Gandhinagar", "State":"Gujarat"},  
       { "Address":"Test 4", "City":"Gandhinagar", "State":"Gujarat"}  
   ]  
}'

DECLARE @JSONOutput AS NVARCHAR(4000)    
SELECT @JSONOutput = JSON_MODIFY(@JSONData, '$.Addresses', JSON_QUERY('[]'))

DECLARE @stm nvarchar(max) = N''
SELECT @stm = @stm + CONCAT(
   'SELECT @JSONOutput = JSON_MODIFY(@JSONOutput, ''append $.Addresses'', JSON_QUERY(@JSONData, ''$.Addresses[',
   [key],
   ']'')); '
)
FROM OPENJSON(@JSONData, '$.Addresses')
WHERE JSON_VALUE([value], '$.Address') <> N'Test 1'

EXECUTE sp_executesql @stm, N'@JSONOutput AS NVARCHAR(4000) OUTPUT, @JSONData AS NVARCHAR(4000)', @JSONOutput OUTPUT, @JSONData
PRINT @JSONOutput

Example for SQL Server 2017+:

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'{  
   "FirstName":"Jignesh",  
   "LastName":"Trivedi",  
   "Code":"CCEEDD",  
   "Addresses":[  
       { "Address":"Test 0", "City":"Gandhinagar", "State":"Gujarat"},  
       { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"},  
       { "Address":"Test 2", "City":"Gandhinagar", "State":"Gujarat"},  
       { "Address":"Test 3", "City":"Gandhinagar", "State":"Gujarat"},  
       { "Address":"Test 4", "City":"Gandhinagar", "State":"Gujarat"}  
   ]  
}'

DECLARE @JSONOutput AS NVARCHAR(4000)    
SELECT @JSONOutput = JSON_MODIFY(@JSONData, '$.Addresses', JSON_QUERY('[]'))
SELECT @JSONOutput = JSON_MODIFY(@JSONOutput, 'append $.Addresses', JSON_QUERY(@JSONData, '$.Addresses[' + [key] + ']'))
FROM OPENJSON(@JSONData, '$.Addresses')
WHERE JSON_VALUE([value], '$.Address') <> N'Test 1'

Update:

If "Addresses" item has a fixed structure, another possible option is to use OPENJSON() and FOR JSON PATH:

SELECT @JSONData = JSON_MODIFY(
   @JSONData,
   '$.Addresses',
   JSON_QUERY(
      (
      SELECT *
      FROM OPENJSON(@JSONData, '$.Addresses') WITH (
         Address nvarchar(1000) '$.Address',
         City nvarchar(1000) '$.City',
         State nvarchar(1000) '$.State'
      )
      WHERE Address <> N'Test 1'
      FOR JSON PATH
      )
   )
)