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]

How is '$.MultiOptions[' + MultiOptionsKey + '].Customers[' + MultiOptionsCustomersKey + '].Orders[' + MultiOptionsOrdersKey + '].PurchasedQty' supposed to work, when orders is not a child of customers? — Alex
– Alex, Commented Aug 1, 2022 at 5:49
@Zhorov : Is there any alternative way to achieve the desired result? — ANR
– ANR, Commented Aug 1, 2022 at 6:32

Charlieface · Accepted Answer · 2022-08-10 08:26:16Z

1

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

edited Aug 10, 2022 at 8:26

answered Aug 1, 2022 at 12:05

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ANR Over a year ago

I am using MS-2016 and getting 'STRING_AGG' is not a recognized built-in function name.

Charlieface Over a year ago

OK added you an option for that also.

Zhorov · Accepted Answer · 2022-08-17 07:00:15Z

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

Collectives™ on Stack Overflow

Update an existing JSON Value inside JSON array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related