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?

Lukasz Szozda · Accepted Answer · 2018-06-20 08:42:13Z

5

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

answered Jun 20, 2018 at 8:42

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Joel Over a year ago

Perfect! However, how would i execute the query to replace the current db-entry?

Lukasz Szozda Over a year ago

@Joel exactly the same. Just put

UPDATE tab SET my_json_column = JSON_MODIFY(my_json_column,'$.time',    FORMAT(DATEADD(hour,-2,JSON_VALUE(my_json_column,'$.time')),'yyyy-MM-ddTHH:mm:ss.fff'))

SuperSecretAndNotSafeFromWork · Accepted Answer · 2018-06-20 08:27:07Z

0

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

edited Jun 20, 2018 at 8:27

answered Jun 20, 2018 at 8:21

SuperSecretAndNotSafeFromWork

1,3012 gold badges17 silver badges34 bronze badges

4 Comments

Joel Over a year ago

I'll look into it. However, a problem i have is that There is no such variable as ChangeTime.LocalTime. How would i access the parameter within the with clause?

SuperSecretAndNotSafeFromWork Over a year ago

Could you not update it outside of the with? I'll update me answer, hold on

Joel Over a year ago

Yeah, right now i'm just parsing out the JSON data into objects, so that i can run DATEADD on the field. After that I want to replace (or update) the current entry with the new value

Joel Over a year ago

Sorry, but i can't reach any variables within the with clause still.

Collectives™ on Stack Overflow

SQL modify and replace json string-value

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related