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

Zhorov · Accepted Answer · 2020-03-23 14:21:27Z

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"}

edited Mar 23, 2020 at 14:21

answered Mar 23, 2020 at 13:08

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

7 Comments

Priyanka Khairnar Over a year ago

I have multiple values, so for that I will need multiple update statements. I am trying to minimize those multiple updates

Zhorov Over a year ago

What is the count of these multiple values?

Priyanka Khairnar Over a year ago

Around 15 to 20

Zhorov Over a year ago

Nested JSON_MODIFY() calls are also an option, but the statement will become complicated.

Priyanka Khairnar Over a year ago

Well, Nested makes it too complicated, I will go with multiple updates then. Thank you so much!

|

Collectives™ on Stack Overflow

Dynamic Update of JSON In SQL Server

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related