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]

CodePudding user response：

I don't think you can use wild cards to modify the JSON content. In your case a possible solution is to parse the stored JSON content (using OPENJSON()) and rebuild the JSON again (uisng 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  
   )
)

CodePudding user response：

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