SQL Server JSON_Modify, how to update everything?

Question

I am trying to update all columns with a value using Json_Modify:

DECLARE @JSON NVARCHAR(MAX) SET @JSON = N'{ "A":1, "TMP": [ {"A":"VALUE1", "B": "VALUE2", "C": 1}, {"A":"VALUE3", "B": "VALUE4", "C": 2}, {"A":"VALUE5", "B": "VALUE6", "C": 3}]} ' SET @JSON = JSON_MODIFY(@JSON, '$.TMP.A', 'JEJE') SELECT * FROM OPENJSON(@JSON, '$.TMP') WITH ( A NCHAR(10), B NCHAR(10), C INT ) 

I need to update all columns of “A” with “JEJE”, for example, it does not work.

Answer 1

Here are two options. Disclaimer: I am not professional in JSON through sql 2016 server, but I hacked something.

Option 1. You clearly create a result set from a JSON string. Why not create a result set and then update it?

 DECLARE @jsontable TABLE (A varchar(50), b varchar(50), c varchar(50)) DECLARE @JSON NVARCHAR(MAX) SET @JSON = N'{ "A":1, "TMP": [ {"A":"VALUE1", "B": "VALUE2", "C": 1}, {"A":"VALUE3", "B": "VALUE4", "C": 2}, {"A":"VALUE5", "B": "VALUE6", "C": 3}]} ' --SET @JSON = JSON_MODIFY(@JSON, '$.TMP.A', 'JEJE') INSERT INTO @jsontable (a,b,c) SELECT * FROM OPENJSON(@JSON, '$.TMP') WITH ( A NCHAR(10), B NCHAR(10), C INT ) UPDATE @jsontable SET a = 'JEJE' SELECT * FROM @jsontable 

Option 2: you can manipulate the JSON components, but you need to pass the index to the function.

 DECLARE @JSON NVARCHAR(MAX) SET @JSON = N'{ "A":1, "TMP": [ {"A":"VALUE1", "B": "VALUE2", "C": 1}, {"A":"VALUE3", "B": "VALUE4", "C": 2}, {"A":"VALUE5", "B": "VALUE6", "C": 3}]} ' SET @JSON = JSON_MODIFY(@JSON, '$.TMP[0].A', 'JEJE') SET @JSON = JSON_MODIFY(@JSON, '$.TMP[1].A', 'JEJE') SET @JSON = JSON_MODIFY(@JSON, '$.TMP[2].A', 'JEJE') SELECT * FROM OPENJSON(@JSON, '$.TMP') WITH ( A NCHAR(10), B NCHAR(10), C INT ) 

Answer 2

I think this may help:

 CREATE PROCEDURE SP__SYSTEM___UPDATE_COLUMN_FROM_JSON @JSON NVARCHAR(MAX) OUTPUT, @COLUMN VARCHAR(50), @NEW_VALUE VARCHAR(50) AS BEGIN DECLARE @QUERY NVARCHAR(MAX) DECLARE @INDEX INT = 0 WHILE @INDEX < (SELECT COUNT(*) FROM OPENJSON(@JSON)) BEGIN SELECT @QUERY = CONCAT(N'SELECT @JSON = JSON_MODIFY(@JSON, ''$[', @INDEX, N'].',@COLUMN,''', ',@NEW_VALUE,')') EXEC SP_EXECUTESQL @QUERY, N'@JSON NVARCHAR(MAX) OUTPUT', @JSON = @JSON OUTPUT SET @INDEX = @INDEX + 1 END END 

And you do it like this:

 BEGIN DECLARE @JSON VARCHAR(MAX) = '[{"ID":1,"USERID":0},{"ID":2,"USERID":0},{"ID":3,"USERID":0}]' EXEC SP__SYSTEM___UPDATE_COLUMN_FROM_JSON @JSON OUTPUT, 'USERID', '100' SELECT @JSON END