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?

What have you tried so far? JSON_MODIFY (Transact-SQL) - Remarks — Thom A
– Thom A ♦, Commented Jul 25, 2019 at 13:58
I used json modify, but it allows to delete only with index (if I am not mistaken) — Sargis Koshkaryan
– Sargis Koshkaryan, Commented Jul 25, 2019 at 14:17
somethig like this - SELECT @JSONData = JSON_MODIFY(@JSONData, '$.Addresses[0]', null) — Sargis Koshkaryan
– Sargis Koshkaryan, Commented Jul 25, 2019 at 14:22
Even when you do have the index, this is way more difficult than it should be, because you can't remove elements by index. There's an open feature request for this. — Jeroen Mostert
– Jeroen Mostert, Commented Jul 25, 2019 at 14:38

Zhorov · Accepted Answer · 2019-08-01 12:52:48Z

10

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

edited Aug 1, 2019 at 12:52

answered Aug 1, 2019 at 9:16

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

3 Comments

Jeroen Mostert Over a year ago

A query of the form SELECT @var = <expression involving @var> FROM <table> is non-deterministic and not guaranteed to be evaluated in the order you expect it to be. It's one of those things that works 99 times out of 100, and then breaks in the next update of the optimizer. In 2017 you can use STRING_AGG to easily build a JSON array, so there's no real need for that trick. For 2016 things get a lot more annoying.

Zhorov Over a year ago

@JeroenMostert Yes, you are right. I should post this as a note at the end of the answer, probably the order of the items in the "Addresses" array is important. Thanks.

Joshua Duxbury Over a year ago

This won't populate any nested properties in the target object. I.e if Addresses had a nested object for the location with lang/long coordinates the object would not appear in the new JSON data

Suraj Rao · Accepted Answer · 2021-01-27 11:06:01Z

We can Open Json then delete the value and replace new json value as follows:

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"}  
        ]  
    }'
            

    SELECT @JSONData=(SELECT oj.FirstName 'FirstName',oj.LastName 'LastName',oj.Code,JSON_VALUE(oj2.Value,'$.Address') AS 'Addresses.Address',JSON_VALUE(oj2.Value,'$.City')  'Addresses.City',
    JSON_VALUE(oj2.Value,'$.State') AS 'Addresses.State' FROM 
    OPENJSON(@JSONData)
    WITH 
    (FirstName NVARCHAR(max),LastName NVARCHAR(max),Code NVARCHAR(max),Addresses NVARCHAR(max) AS JSON)oj
    CROSS APPLY OPENJSON(oj.Addresses) oj2
    WHERE JSON_VALUE(oj2.Value,'$.Address')<>'Test 1'
    FOR JSON PATH)
        PRINT @JSONData

Collectives™ on Stack Overflow

Remove element from json array by condition sql server 2016

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related