Update value from json dictionary by key in MSSQL

Question

I need to update a dictionary value in a field in a table that contains json in Microsoft SQL.

So if the table and data is this:

CREATE TABLE Visits
(   
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [AdditionalInfo] NVARCHAR(4000) NULL
)

insert into visits
   (id,AdditionalInfo)
values
   (newid(),'{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z"}}')
insert into visits
   (id,AdditionalInfo)
values
   (newid(),'{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z","7f90a25b-f5d4-eb11-bacb-0022481aa333":"2022-10-11T14:42:00Z"}}')

I want to update the time of the last visit, given an arbitary key.

For example, where the key is '7f90a25b-f5d4-eb11-bacb-0022481aa333', I want it now to be '2023-07-06T13:00:00Z'

So the second row with the AdditionalInfo field that was:

{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z","7f90a25b-f5d4-eb11-bacb-0022481aa333":"2022-10-11T14:42:00Z"}}

should now be:

{"LastVisit":{"4d635993-3829-ec11-b6e6-000d3a0cc942":"2022-10-11T12:00:00Z","7f90a25b-f5d4-eb11-bacb-0022481aa333":"2023-07-06T13:00:00Z"}}

and the first row is left as it was.

Any help would be apprieciated.

I did get as far as to be able to select the row:

select id, additionalinfo from visits
where exists(select [key] , [value] from openjson(additionalinfo, '$.LastVisit') where [key] = '7f90a25b-f5d4-eb11-bacb-0022481aa333')

But I got stuck getting any further with updating the value. I did look into json_modify, but that needs a jsonpath, and I can't find the syntax for a dictionary (key/value, where the keys are all different).

Answer 1

The actual approach depends on the structure of your JSON content, but in case of JSON object(s) without nested JSON arrays, a statement using JSON_MODIFY() is an option:

UPDATE Visits
SET [AdditionalInfo] = JSON_MODIFY(
   [AdditionalInfo],
   '$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"',
   '2023-07-06T13:00:00Z'
)
WHERE JSON_VALUE(
  [AdditionalInfo],
  '$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"'
) IS NOT NULL  

Starting from SQL Server 2022, you may use JSON_PATH_EXISTS() in the WHERE clause:

...
WHERE JSON_PATH_EXISTS(
  [AdditionalInfo],
  '$.LastVisit."7f90a25b-f5d4-eb11-bacb-0022481aa333"'
) = 1