SQL Server JSON_Modify, How to Update all?
14,114
Solution 1
Here are two options. Disclaimer: I am not a pro at JSON through sql server 2016, but I have hacked some stuff together.
Option 1: You are clearly creating a result set from the JSON string. Why not create the 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 have 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 )
Solution 2
If you are storing the array in a field, you can do the following:
UPDATE some_table SET
some_field = '[' + (
SELECT
STRING_AGG(JSON_MODIFY([value],'$.A', 'JEJE'), ',') WITHIN GROUP (ORDER BY CAST([key] AS int))
FROM
OPENJSON(some_field)
) + ']'
Solution 3
I think this can be 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 execute it like that:
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
Author by
mdelphi
Updated on June 05, 2022Comments
-
mdelphi almost 2 years
i am trying update all columns with a value with 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 update all columns "A" with "JEJE" for example, it is not working.