sql-serverjsonsql-server-2016sql-server-json

SQL Server JSON_Modify, How to Update all?


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.


Solution

  • 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 )