Update an existing JSON Value inside JSON array

Question

DECLARE @jsontable TABLE (JsonData nvarchar(max) NULL)

DECLARE @JsonData NVARCHAR(MAX) = N'
{
  "Sections": {
    "SectionType": false,
    "SectionName": "Section1"
  },
  "MultiOptions": [
    {
      "Customers": {
        "CustomerName": "name1",
        "Address": "",
        "Source1" : "1"
      },
      "Orders": {
        "OrderName": "item1",
        "Qty": 1,
        "Dest1" : "0",
        "PurchasedQty" : "0"
      },
      "IsMainOption": true
    },
    {
      "Customers": {
        "CustomerName": "name2",
        "Address": "",
        "Source1" : "2"
      },
      "Orders": {
        "OrderName": "item2",
        "Qty": 2,
        "Dest1" : "0",
        "PurchasedQty" : "0"
      },
      "IsMainOption": false
    },
    {
      "Customers": {
        "CustomerName": "name3",
        "Address": "",
        "Source1" : "3"
      },
      "Orders": {
        "OrderName": "item2",
        "Qty": 3,
        "Dest1" : "0",
        "PurchasedQty" : "0"
      },
      "IsMainOption": false
    }
  ],
  "DateUpdated": "2022-05-24",
  "WhoUpdated": 1
}'

INSERT INTO @jsontable
    SELECT @JsonData

;WITH cte AS 
(
    SELECT 
        MultiOptions.[key] AS MultiOptionsKey,
        MultiOptionsCustomers.[key] AS MultiOptionsCustomersKey,
        MultiOptionsCustomers.[value] AS MultiOptionsCustomersValue,
        MultiOptionsOrders.[key] AS MultiOptionsOrdersKey,
        MultiOptionsOrders.[value] AS MultiOptionsOrdersValue,
        JsonData 
    FROM
        @jsontable a 
    CROSS APPLY 
        OPENJSON(JsonData, '$.MultiOptions') MultiOptions
    CROSS APPLY 
        OPENJSON(MultiOptions.value, '$.Customers') MultiOptionsCustomers
    CROSS APPLY 
        OPENJSON(MultiOptions.value, '$.Orders' ) AS MultiOptionsOrders 
)
UPDATE cte 
SET JsonData = JSON_MODIFY(JsonData, '$.MultiOptions[' + MultiOptionsKey + '].Customers[' + MultiOptionsCustomersKey + '].Orders[' + MultiOptionsOrdersKey + '].PurchasedQty', 'Qty#Value')
--select * from cte;

I'm getting this error:

The argument 2 of the "JSON_MODIFY" must be a string literal.

when I execute this query.

I need to update below for all items inside array:

UPDATE [MultiOptions.Orders.Dest1] = [MultiOptions.Customers.Source1]
UPDATE [MultiOptions.Customers.PurchasedQty] = [MultiOptions.Customers.Qty]

Answer 1

I don't think you can use wild cards to modify all the items in the $.MultiOptions JSON array at once. In your case a possible solution is to parse the stored JSON content (using OPENJSON()) and rebuild the JSON again (using FOR JSON PATH):

UPDATE @jsontable
SET JsonData = JSON_MODIFY(
   JsonData,
   '$.MultiOptions',
   (
   SELECT 
      CustomerName AS [Customers.CustomerName],
      Address AS [Customers.Address], 
      Source1 AS [Customers.Source1], 
      OrderName AS [Orders.OrderName], 
      Qty AS [Orders.Qty], 
      Source1 AS [Orders.Dest1],      -- updated value
      Qty AS [Orders.PurchasedQty],   -- updated value
      IsMainOption AS [IsMainOption]
   FROM OPENJSON(JsonData, '$.MultiOptions') WITH (
      CustomerName nvarchar(max) '$.Customers.CustomerName', 
      Address nvarchar(max) '$.Customers.Address', 
      Source1 nvarchar(max) '$.Customers.Source1', 
      OrderName nvarchar(max) '$.Orders.OrderName', 
      Qty int '$.Orders.Qty', 
      IsMainOption bit '$.IsMainOption'
   )
   FOR JSON PATH  
   )
)

Answer 2

You don't necessarily have to rebuild the whole JSON. In your case, because you only have one level of arrays to feed through OPENJSON, you can just rebuild that, and use JSON_VALUE and JSON_MODIFY to access the various values.

Unfortunately, SQL Server does not have JSON_AGG. So to aggregate whole JSON objects you need STRING_AGG to aggregate, and also JSON_QUERY to prevent double-escaping

UPDATE t
SET JsonData =
  JSON_MODIFY(
    JsonData,
    '$.MultiOptions',
    JSON_QUERY((
        SELECT
          '[' + STRING_AGG(
            JSON_MODIFY(
              JSON_MODIFY(
                arr.value,
                '$.Orders.Dest1',
                JSON_VALUE(arr.value, '$.Customers.Source1')
              ),
              '$.Customers.PurchasedQty',
              JSON_VALUE(arr.value, '$.Customers.Qty')
            ),
            ','
          ) + ']'
        FROM OPENJSON(t.JsonData, '$.MultiOptions') arr
    ))
  )
FROM jsontable t;

SQL Fiddle

For older versions of SQL Server, it's even more complicated, as you need to use FOR XML PATH('') to aggregate, then manipulate it further to add [] and remove the leading comma.

UPDATE t
SET JsonData =
  JSON_MODIFY(
    JsonData,
    '$.MultiOptions',
    JSON_QUERY(
      STUFF(
        (
          SELECT
            ',' +
            JSON_MODIFY(
              JSON_MODIFY(
                arr.value,
                '$.Orders.Dest1',
                JSON_VALUE(arr.value, '$.Customers.Source1')
              ),
              '$.Customers.PurchasedQty',
              JSON_VALUE(arr.value, '$.Customers.Qty')
            )
          FROM OPENJSON(t.JsonData, '$.MultiOptions') arr
          FOR XML PATH(''), TYPE
        ).value('text()[1]','nvarchar(max)'),
        1, 1, '['
      )
      + ']'
    )
  )
FROM jsontable t;

SQL Fiddle