json_modify set multiple keys in one update statement-CodePudding

I'm having trouble finding the documentation for this, or at least understanding it to my needs.

I have a column in a table that is meant to hold metadata for my rows in form of JSON objects. I'm working on a query, where I want to add two key-values to this column in one update-statement. This is what I've got, that works to update one:

UPDATE list SET 
    list.JsonData = JSON_MODIFY(list.JsonData, '$.Key1', 'Value1')
FROM SomeTable list

I'd like to add (pseudo) '.$Key2' with 'Value2' in the same update. Any suggestions?

CodePudding user response：

Just add an additional JSON_MODIFY, like this:

drop table if exists #list
go
create table #list(id int, JsonData nvarchar(max))
insert into #list(id,JsonData) values (1,'{"a":3}')

UPDATE #list 
SET JsonData = JSON_MODIFY(JSON_MODIFY(JsonData, '$.Key1', 'Value1'),'$.Key2','Value2')

select * from #list

outputs

id          JsonData
----------- ----------------
1           {"a":3,"Key1":"Value1","Key2":"Value2"}