SQL modify and replace json string-value

Question

Is it possible to change the values of sub-json-data in the following manner?

DECLARE @json NVARCHAR(MAX) = "{"message":"Machine is down","machineId":"165ACE37-4E2C-4D44-9D14-F9E2CB2C2C13","machineName":"1501","ipAddress":"192.168.150.101","time":"2018-05-20T18:33:23.171"}"

SELECT * FROM OPENJSON(@json) 
WITH (  message varchar(200) '$.message',
    machineId varchar(200) '$.machineId',
    machineName int '$.machineName',
    ipAddress varchar(200) '$.ipAddress',
    LocalTime datetime2(7) '$.time'
) AS ChangeTime

I want to change the LocalTime variable i created and then replace the current one.

I want to remove two hours from the $.time parameter.

Something like this:

UPDATE Table SET ChangeTime.LocalTime = DATEADD(hour,-2,ChangeTime.LocalTime)

Input: " time":"2018-05-20T18:33:23.171"

output: "time":"2018-05-20T16:33:23.171"

How would i accomplish this?

Answer 1

You could use JSON_MODIFY:

Updates the value of a property in a JSON string and returns the updated JSON string.

SET @json=JSON_MODIFY(@json,'$.time',
   FORMAT(DATEADD(hour,-2,JSON_VALUE(@json,'$.time')),'yyyy-MM-ddTHH:mm:ss.fff'));

SELECT * FROM OPENJSON(@json) 
WITH (  message varchar(200) '$.message',
    machineId varchar(200) '$.machineId',
    machineName int '$.machineName',
    ipAddress varchar(200) '$.ipAddress',
    LocalTime datetime2(7) '$.time'
) AS ChangeTime;

DBFIddle Demo

Answer 2

Maybe something like this:

LocalTime - INTERVAL '2 hours'

With interval you can remove or add any time denomination. Like days, months, years, seconds etc

WITH (  message varchar(200) '$.message',
    machineId varchar(200) '$.machineId',
    machineName int '$.machineName',
    ipAddress varchar(200) '$.ipAddress',
    LocalTime datetime2(7) '$.time'
) AS ChangeTime
SELECT LocalTime - INTERVAL '2 hours'
FROM ChangeTime