JSON_MODIFY Remove Array Objects

Question

How do you remove an object from a VARCHAR(MAX) JSON array?

Setup

DECLARE @Json VARCHAR(MAX) =
JSON_QUERY('[{"u":"user1","i":"item1"},{"u":"user2","i":"item2"}]')

SELECT @Json

DECLARE @NewJson VARCHAR(MAX) =
JSON_QUERY('{"u":"user3","i":"item3"}')

SELECT @NewJson

Result

[{"u":"user1","i":"item1"},{"u":"user2","i":"item2"}]

{"u":"user3","i":"item3"}

Append `@NewJson` to `@Json`

SELECT JSON_MODIFY(@Json, 'append $', JSON_QUERY(@NewJson))

Result

[{"u":"user1","i":"item1"},{"u":"user2","i":"item2"},{"u":"user3","i":"item3"}]

All good.

Remove Index `0` from `@Json`

SELECT JSON_MODIFY(@Json, '$[0]', NULL)

Result

[null,{"u":"user2","i":"item2"}]

I don't want index 0 to be null. I want it removed.

I can't find anything in the docs that state how to remove an object from an array.

Alan Burstein · Accepted Answer · 2017-04-17 13:36:18Z

1

I haven't worked with json but, since its a string you could do this;

SET @Json = JSON_MODIFY(@Json, '$[0]', NULL);
SET @Json = STUFF(@Json, CHARINDEX ('null,', 5, '');

Note That I can't test this code at the moment.

answered Apr 17, 2017 at 13:36

Alan Burstein

8,0081 gold badge18 silver badges19 bronze badges

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

1 Comment

kspearrin Over a year ago

Pretty hacky, but I guess it works if TSQL doesn't provide a native function for this.

Collectives™ on Stack Overflow

JSON_MODIFY Remove Array Objects

Setup

Append `@NewJson` to `@Json`

Remove Index `0` from `@Json`

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Setup

Append @NewJson to @Json

Remove Index 0 from @Json

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related

Append `@NewJson` to `@Json`

Remove Index `0` from `@Json`