clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228823 fiddles created (16676 in the last week).

CREATE TABLE t(i INT, c NVARCHAR(MAX) CHECK (ISJSON(c)> 0 ))
 hidden batch(es)


INSERT INTO t VALUES ( 1, '[{"id":"101","name":"John"}, {"id":"102","name":"peter"}]')
1 rows affected
 hidden batch(es)


SELECT * FROM t
i c
1 [{"id":"101","name":"John"}, {"id":"102","name":"peter"}]
 hidden batch(es)


SELECT * FROM t CROSS APPLY OPENJSON(c) s WHERE i = 1 AND JSON_VALUE(s.value, '$.id')=102
i c key value type
1 [{"id":"101","name":"John"}, {"id":"102","name":"peter"}] 1 {"id":"102","name":"peter"} 5
 hidden batch(es)


WITH cte AS ( SELECT * FROM t CROSS APPLY OPENJSON(c) s WHERE i = 1 AND JSON_VALUE(s.value, '$.id')=102 ) UPDATE cte SET c = JSON_MODIFY(c, '$[' + cte.[key] + '].name', 'Joe')
1 rows affected
 hidden batch(es)


SELECT * FROM t
i c
1 [{"id":"101","name":"John"}, {"id":"102","name":"Joe"}]
 hidden batch(es)