First insert is not a problem. But update JSON value getting error. It is getting error because I am using dynamic value. However I cannot figure out how I can.
DECLARE @Index INT = (
SELECT rn - 1
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn, value
FROM OPENJSON(@CurrentJson, '$.RatedRecipes')
) AS numbered
WHERE JSON_VALUE(value, '$.Id') = @ArticleId);
IF @Index IS NOT NULL
BEGIN
SET @CurrentJson = JSON_MODIFY(@CurrentJson, '$.RatedRecipes[' + CAST(@Index AS NVARCHAR(5)) + '].Rate', @Rate);
END
ELSE
BEGIN
DECLARE @RateRecipe NVARCHAR(MAX);
SET @RateRecipe = CONCAT('{"Id": "', @ArticleId, '", "Rate": ', @Rate, '}');
SET @CurrentJson = JSON_MODIFY(@CurrentJson, 'append $.RatedRecipes', JSON_QUERY(@RateRecipe));
END
If I use like below not a problem. But I cannot
SET @CurrentJson = JSON_MODIFY(@CurrentJson, '$.RatedRecipes[0].Rate', @Rate);
That is the CurrentJson
{"FollowedAuthors": [], "Recipes": [{"Id": "B88EEE77-B779-491C-97C3-CDE8C1E7DF9F", "Title": "Tart", "Url": "/recipe-tart", "Type": "Liked"}], "RatedRecipes": [{"Id": "499D486C-0CF5-4708-A818-2F86E23A9C65", "Rate": 5},{"Id": "B8522F7C-08C9-4DB9-86A3-5494461451DF", "Rate": 2}]}
@@VERSION returns
Microsoft SQL Server 2016 (SP2) (KB4052908) – 13.0.5026.0 (X64) Mar 18
2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise Edition
(64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )
(Hypervisor)
12
Your current statement works on SQL Server 2017+. SQL Server 2016 doesn’t support a variable or expression as the value of path and this is the explanation for the error in the statement. One solution in this situation is a different approach (working on SQL Server 2016+). The idea is to parse the $.RatedRecipes part of the JSON content and transform it into a table with OPENJSON(), update/insert the new values using a SET-based approach, export the table as JSON and finally modify the original JSON with JSON_MODIFY():
SET @CurrentJson = JSON_MODIFY(
@CurrentJson,
'$.RatedRecipes',
(
SELECT COALESCE(v.Id, j.Id) AS Id, COALESCE(v.Rate, j.Rate) AS Rate
FROM OPENJSON(@CurrentJson, '$.RatedRecipes') WITH (
Id uniqueidentifier '$.Id',
Rate INT '$.Rate'
) j
FULL OUTER JOIN (VALUES (@ArticleId, @Rate)) v (Id, Rate) ON j.Id = v.Id
FOR JSON AUTO
)
);