CODEWITHSUNDEEP
sql-server
Gopi
Gopi

Reputation: 5887

Update JSON value - MSSQL

In MSSQL I need to update the actualWatchedTime with totalDuration.

Current Table

Id  VideoId UserId  ProgressJson
1   1       1       {"actualWatchedTime":228,"currentWatchTime":3,"totalDuration":657}
2   2       1       {"actualWatchedTime":328,"currentWatchTime":23,"totalDuration":349}
3   3       1       {"actualWatchedTime":28,"currentWatchTime":2,"totalDuration":576}
4   1       2       {"actualWatchedTime":82,"currentWatchTime":103,"totalDuration":576}
5   2       2       {"actualWatchedTime":280,"currentWatchTime":253,"totalDuration":456}

Expected Table

Id  VideoId UserId  ProgressJson
1   1       1       {"actualWatchedTime":657,"currentWatchTime":3,"totalDuration":657}
2   2       1       {"actualWatchedTime":349,"currentWatchTime":23,"totalDuration":349}
3   3       1       {"actualWatchedTime":576,"currentWatchTime":2,"totalDuration":576}
4   1       2       {"actualWatchedTime":576,"currentWatchTime":103,"totalDuration":576}
5   2       2       {"actualWatchedTime":456,"currentWatchTime":253,"totalDuration":456}

How do I do that?

Upvotes: 0

Views: 58

Answers (1)

lptr
lptr

Reputation: 6788

declare @t table
(
id int,
ProgressJson nvarchar(500)
);

insert into @t(id, ProgressJson)
values
(1, N'{"actualWatchedTime":228,"currentWatchTime":3,"totalDuration":657}'),
(2, N'{"actualWatchedTime":328,"currentWatchTime":23,"totalDuration":349}'),
(3, N'{"actualWatchedTime":28,"currentWatchTime":2,"totalDuration":576}'),
(4, N'{"actualWatchedTime":82,"currentWatchTime":103,"totalDuration":576}'),
(5, N'{"actualWatchedTime":280,"currentWatchTime":253,"totalDuration":456}');

select *
from @t;

update @t
set ProgressJson = JSON_MODIFY(ProgressJson,'$.actualWatchedTime', cast(json_value(ProgressJson, '$.totalDuration') as int));

select *
from @t;

Upvotes: 1

Related Questions