Change Existing JSON value "JSON_MODIFY" must be a string literal

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)

Solution

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