Dynamic Update of JSON In SQL Server

Question

One of the column I have, have values like this. If my JSON has key id then update its value to new one, if my JSON JSON has key workload then update its value to new one Like this, I have multiple entries to update with the new values.

Currently, I am trying to loop into the table, parsing one JSON value at a time. I have tried using CASE statements.

BEGIN
    SET @MyCursor = CURSOR FOR
    select key_value  from @Template
        where key_type = 5

    OPEN @MyCursor 
    FETCH NEXT FROM @MyCursor 
    INTO @MyField

    WHILE @@FETCH_STATUS = 0
    BEGIN

      update @Template
      set key_value =
      case 
      when 'id' in (select [key] from openjson(@MyField))
      then 
        JSON_MODIFY(@MyField, '$.id', '1')

      when 'workload' in (select [key] from openjson(@MyField))
      then 
        JSON_MODIFY(@MyField, '$.workload', '5')

      ELSE '3'
      END 
      -- Fetch next
      FETCH NEXT FROM @MyCursor 
      INTO @MyField 
    END; 
    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

This gives the result as below

Please suggest what have I done wrong

Answer 1

If I understand the question correctly, you don't need a cursor, just update statements:

Table:

CREATE TABLE Data (
   key_index int,
   key_value nvarchar(max)
)
INSERT INTO Data
   (key_index, key_value)
VALUES
   (0, N'{"id": 219002, "name": "provider", "protokol": "htpps"}'),
   (1, N'{"srp": "srp", "workload": "2"}')

Statement with multiple updates:

UPDATE d
SET d.key_value = JSON_MODIFY(d.key_value, '$.id', 1)
FROM Data d
CROSS APPLY OPENJSON(d.key_value) j
WHERE j.[key] = 'id'
UPDATE d
SET d.key_value = JSON_MODIFY(d.key_value, '$.workload', '5')
FROM Data d
CROSS APPLY OPENJSON(d.key_value) j
WHERE j.[key] = 'workload'

-- or

UPDATE Data
SET key_value = JSON_MODIFY(
    key_value, 
    '$.id', 
    CASE WHEN JSON_VALUE(key_value, 'lax $.id') IS NOT NULL THEN 2 END
)
UPDATE Data
SET key_value = JSON_MODIFY(
    key_value, 
    '$.workload', 
    CASE WHEN JSON_VALUE(key_value, 'lax $.workload') IS NOT NULL THEN '5' END
)

Statement with nested JSON_MODIFY() calls:

UPDATE Data
SET 
   key_value = JSON_MODIFY(
      JSON_MODIFY(
         key_value, 
         '$.id', 
         CASE WHEN JSON_VALUE(key_value, 'lax $.id') IS NOT NULL THEN 2 END
      ),
      '$.workload', 
      CASE WHEN JSON_VALUE(key_value, 'lax $.workload') IS NOT NULL THEN '5' END
   )   

Result:

key_index   key_value
0           {"id": 1, "name": "provider", "protokol": "htpps"}
1           {"srp": "srp", "workload": "5"}