SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Data(id int identity primary key, json nvarchar(max));
INSERT Data VALUES (N'{
    "name": "target_name",
    "value": 1,
    "some_arr": [
        {
            "foo": "foo",
            "name": "foo_name"
        }, 
        {
            "name": "target_name",
            "value": 5
        }, 
        {
            "foo2": "foo",
            "name": "some_name",
            "foo3": {
                "name": "target_name",
                "value": 7
            }
        }
    ]
}');
1 rows affected

CREATE FUNCTION dbo.ChangeJsonObject(@json nvarchar(max))
RETURNS nvarchar(max)
AS BEGIN
  DECLARE @name nvarchar(max) = JSON_VALUE(@json, '$.name');
  
  RETURN (
    SELECT CONCAT('{', STRING_AGG(j.[key] + N':' + v.NewValue, ','), '}')
    FROM OPENJSON(@json) j
    CROSS APPLY (VALUES(
        CASE j.type
        WHEN 0 THEN
          N'null'
        WHEN 1 THEN
          N'"' + j.value + N'"'
        WHEN 2 THEN
          IIF(
            j.[key] = 'value' AND @name = 'target_name',
            CAST(CAST(j.value AS int) + 1 AS nvarchar(max)),
            j.value
          )
        WHEN 4 THEN
          dbo.ChangeJsonArray(j.value)
        WHEN 5 THEN
          dbo.ChangeJsonObject(j.value)
        ELSE
          j.value
        END
    )) v(NewValue)
  );
END;
CREATE FUNCTION dbo.ChangeJsonArray(@json nvarchar(max))
RETURNS nvarchar(max)
AS BEGIN
  RETURN (
    SELECT CONCAT('[', STRING_AGG(v.NewValue, ','), ']')
    FROM OPENJSON(@json) j
    CROSS APPLY (VALUES(
        CASE j.type
        WHEN 0 THEN
          N'null'
        WHEN 2 THEN
          N'"' + j.value + N'"'
        WHEN 4 THEN
          dbo.ChangeJsonArray(j.value)
        WHEN 5 THEN
          dbo.ChangeJsonObject(j.value)
        ELSE
          j.value
        END
    )) v(NewValue)
  );
END;
SELECT ISJSON(d.json),
  dbo.ChangeJsonObject(d.json)
FROM Data d


(No column name)
(No column name)


1
{name:"target_name",value:2,some_arr:[{foo:"foo",name:"foo_name"},{name:"target_name",value:6},{foo2:"foo",name:"some_name",foo3:{name:"target_name",value:8}}]}