Firstly, you cannot actually modify multiple nodes at the same time using .modify anyway. You would normally do that in a loop or a recursive CTE, or by rebuilding the XML.
It is far more complicated to do this with JSON, as JPath in SQL Server does not support any form of predicate. For every level that needs a predicate, you need to break it out separately.
Let's assume you want to only do it on the first item. You could use JSON_MODIFY, but you would need to build the JPath dynamically, which only works on SQL Server 2017+.
UPDATE t
SET json = JSON_MODIFY(t.json, '$.Users[' + j.[key] + '].fname', 'Nat')
FROM t
CROSS APPLY (
SELECT TOP 1 j.[key]
FROM OPENJSON(t.json, '$.Users') j
WHERE JSON_VALUE(j.value, '$.user_id') = '2'
) j;
Or for a variable you can do
SET @json = JSON_MODIFY(@json, '$.Users[' + (
SELECT TOP 1 j.[key]
FROM OPENJSON(@json, '$.Users') j
WHERE JSON_VALUE(j.value, '$.user_id') = '2'
) + '].fname', 'Nat');
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1c445798717b77a805bb0a6d93b5af43
As you can see, it gets complex and only works with one value, and only on SQL Server 2017+.
A better alternative, especially if you have multiple values, is to rebuild the JSON.
You could go the whole hog and rebuild each object:
UPDATE t
SET json = j.json
FROM t
CROSS APPLY (
SELECT
user_id,
fname = CASE WHEN user_id = 2 THEN 'Nat' ELSE fname END,
lname,
email,
gender
FROM OPENJSON(t.json, '$.Users')
WITH (
user_id int,
fname varchar(50),
lname varchar(50),
email varchar(255),
gender char(1)
) j
FOR JSON PATH, ROOT('Users')
) j(json);
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9dce23067a5b7c142697fde4978bbd3c
But the easiest way in a lot of cases is to rebuild only down to the level above the one you need to change, and do the final one with JSON_MODIFY.
In this particular case, that means you just need to do an array break-out with OPENJSON and no schema, then conditionally JSON_MODIFY the objects. SQL Server doesn't have JSON_AGG, so we need to hack that with STRING_AGG:
UPDATE t
SET json = j.json
FROM t
CROSS APPLY (
SELECT Users = JSON_QUERY('[' + STRING_AGG(CAST(
CASE WHEN JSON_VALUE(j.value, '$.user_id') = '2'
THEN JSON_MODIFY(j.value, '$.fname', 'Nat')
ELSE j.value END
AS nvarchar(max)), ',') + ']')
FROM OPENJSON(t.json, '$.Users') j
FOR JSON PATH
) j(json);
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3a61f00d991e13e7fa56dfa5017b2525
