Is there any insert or update value in json array by index. I have a column with such values:
["qwe","123"] and I want to insert new value in the middle:
["qwe","asd","123"] or remove one:
["123"] how can it be done using JSON_MODIFY?
I've only managed to append the last value:
select JSON_MODIFY([Column], 'append $', '') from MyTable or update change the value in the middle to null:
select JSON_MODIFY([Column], '$[1]', NULL) from MyTable and then REPLACE it to nothing.
If you want to insert new value before the last value, you can append the last value again as n+1 , then replace the n value:
DECLARE @Data TABLE ( [ID] INT ,[Column] NVARCHAR(MAX) ); INSERT INTO @Data ([ID], [Column]) VALUES (1, '["qwe","123"]'); SELECT JSON_MODIFY(JSON_MODIFY([Column], 'append $', JSON_VALUE([Column], '$[1]')), '$[1]', 'asd') FROM @Data WHERE [ID] = 1; 
Now, if we have many elements, and if we want to insert new element on the second or on the third index, the above will not work as we need to swap all values after the new one.
To do this, we can use the OPNEJSON function to split the values, then insert new value with appropriate index and concatenate the values using FOR XML or SRING AGGREGATE function.
DECLARE @Data TABLE ( [ID] INT ,[Column] NVARCHAR(MAX) ); INSERT INTO @Data ([ID], [Column]) VALUES (2, '["qwe","123", "125" ,"126"]'); SELECT '[' + STUFF ( ( SELECT ',' + [value] AS [value] FROM ( SELECT JS.[value] ,JS.[key] * 1.0 FROM @Data DS CROSS APPLY OPENJSON([Column]) JS WHERE DS.[ID] = 2 UNION ALL SELECT 'asd' ,0.1 ) DS ([value], [key]) ORDER BY [key] FOR XML PATH, TYPE ).value('.', 'NVARCHAR(MAX)') ,1 ,1 ,'' ) +']'; 
I think there isn't a normal way to do it.
I've found only the following way how to do it
-- create and fill #TestData SELECT '["qwe","asd","123"]' json_str INTO #TestData UNION ALL SELECT '["zxc","asd","321"]' UNION ALL SELECT '[]' UNION ALL SELECT '["qwe","asd","12\"3"]' -- escaped quotes UNION ALL SELECT '["qwe","asd","<123>"]' -- special characters -- delete demo SELECT json_str, JSON_QUERY(CONCAT('[',STUFF(( SELECT CONCAT(',"',REPLACE([value],'"','\"'),'"') FROM OPENJSON(json_str, '$') WHERE [key]<>1 -- exclude element with index 1 ORDER BY [key] FOR XML PATH('')),1,1,''),']')) new_json_str FROM #TestData -- insert demo SELECT json_str, JSON_QUERY(CONCAT('[',STUFF(( SELECT CONCAT(',"',REPLACE([value],'"','\"'),'"') FROM ( SELECT 1 RowPriority,[key],[value] FROM OPENJSON(json_str, '$') UNION ALL SELECT 2 RowPriority, 0, -- add after key with index 0 'new value' -- value ) q ORDER BY [key],RowPriority -- sort values FOR XML PATH('')),1,1,''),']')) new_json_str FROM #TestData DROP TABLE #TestData And one more example to delete values by another condition and to insert several values
-- create and fill #TestData SELECT '["qwe","asd","123"]' json_str INTO #TestData UNION ALL SELECT '["zxc","asd","321"]' UNION ALL SELECT '[]' -- empty array UNION ALL SELECT '["qwe","asd","12\"3"]' -- escaped quotes UNION ALL SELECT '["qwe","asd","<123>"]' -- special characters -- delete with another condition demo SELECT json_str, JSON_QUERY(CONCAT('[',STUFF(( SELECT CONCAT(',"',REPLACE([value],'"','\"'),'"') FROM OPENJSON(json_str, '$') WHERE [value] NOT IN('qwe','123') -- another delete condition ORDER BY [key] FOR XML PATH('')),1,1,''),']')) new_json_str FROM #TestData -- insert several values demo SELECT json_str, JSON_QUERY(CONCAT('[',STUFF(( SELECT CONCAT(',"',REPLACE([value],'"','\"'),'"') FROM ( SELECT 1 RowPriority,0 NewValOrder,[key],[value] FROM OPENJSON(json_str, '$') UNION ALL SELECT 2 RowPriority,NewValOrder,AfterPosition,[value] FROM (VALUES(0,1,'new value 1'),(0,2,'new value 2')) v(AfterPosition,NewValOrder,[value]) ) q ORDER BY [key],RowPriority,NewValOrder -- sort values FOR XML PATH('')),1,1,''),']')) new_json_str FROM #TestData DROP TABLE #TestData Hope this variant will suit you.
A variant with STRING_AGG instead FOR XML PATH('')
-- create and fill #TestData SELECT '["qwe","asd","123"]' json_str INTO #TestData UNION ALL SELECT '["zxc","asd","321"]' UNION ALL SELECT '[]' -- empty array UNION ALL SELECT '["qwe","asd","12\"3"]' -- escaped quotes UNION ALL SELECT '["qwe","asd","<123>"]' -- special characters -- delete with another condition demo SELECT json_str, JSON_QUERY(CONCAT('[', ( SELECT STRING_AGG(CONCAT('"',REPLACE([value],'"','\"'),'"'),',') WITHIN GROUP(ORDER BY [key]) FROM OPENJSON(json_str, '$') WHERE [value] NOT IN('qwe','123') -- another delete condition ),']')) new_json_str FROM #TestData -- insert several values demo SELECT json_str, JSON_QUERY(CONCAT('[', ( SELECT STRING_AGG(CONCAT('"',REPLACE([value],'"','\"'),'"'),',') WITHIN GROUP(ORDER BY [key],RowPriority,NewValOrder) FROM ( SELECT 1 RowPriority,0 NewValOrder,[key],[value] FROM OPENJSON(json_str, '$') UNION ALL SELECT 2 RowPriority,NewValOrder,AfterPosition,[value] FROM (VALUES(0,1,'new value 1'),(0,2,'new value 2')) v(AfterPosition,NewValOrder,[value]) ) q ),']')) new_json_str FROM #TestData DROP TABLE #TestData Maybe the last variant will be better because construction FOR XML PATH('') replaces some special characters (for example: < - <).
REPLACE([value],'"','\"') into all my queries. ) You can check again.ORDER BY [key] into the old queries and added one more variant with STRING_AGG instead FOR XML PATH(''). Look at the last query.