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.