update json value to null
According to MS Docs about JSON_MODIFY:

+----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+
| Existing value | Path exists | Lax mode                                                                                | Strict mode                     |
+----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+
| Not NULL       | Yes         | Update the existing value.                                                              | Update the existing value.      |
+----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+
| Not NULL       | No          | Try to create a new key:value pair on the specified path.This may fail.                 | Error - INVALID_PROPERTY        |
|                |             | For example, if you specify the path $.user.setting.theme, JSON_MODIFY does not insert  |                                 |
|                |             | the key theme if the $.user or $.user.settings objects do not exist, or if settings     |                                 |
|                |             | is an array or a scalar value.                                                          |                                 |
+----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+
| NULL           | Yes         | Delete the existing property.                                                           | Set the existing value to null. |
+----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+
| NULL           | No          | No action. The first argument is returned as the result.                                | Error - INVALID_PROPERTY        |
+----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+


Simply add the keyword strict in front of the json path, using the example supplied by the docs:

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}';
SET @info=JSON_MODIFY(@info,'strict $.name',NULL):
PRINT @info;


returns

{"name":null,"skills":["C#","SQL"]}


db&lt;>fiddle here