Add new property to nested object in all objects of JSON array with SQL

Question

I have a SQL table Templates, which has a column JsonContent. I would like to modify the content of JsonContent with SQL.

Each JsonContent has serialized JSON array of objects. For example:

[
   {
      "Name":"Test name",
      "Options":[
         {
            "Name":"1",
            "Value":1
         },
         {
            "Name":"2",
            "Value":2
         },
         {
            "Name":"3",
            "Value":3
         }
      ]
   },
   {
      "Name":"Another name",
      "Options":null
   }
]

I would like to add new property to each object in Options, which will be named IsDeleted and set the value to false. For example:

[
   {
      "Name":"Test name",
      "Options":[
         {
            "Name":"1",
            "Value":1,
            "IsDeleted": false
         },
         {
            "Name":"2",
            "Value":2,
            "IsDeleted": false
         },
         {
            "Name":"3",
            "Value":3,
            "IsDeleted": false
         }
      ]
   },
   {
      "Name":"Another name",
      "Options":null
   }
]

How can I modify the first example with SQL and get the second example as a result?

Answer 1

The JSON_MODIFY(expression, path, newValue) function is usually used to update the value of an existing JSON property or insert (delete) a specified "key":value pair. The problem here is that this function doesn't support wildcard characters for the path parameter.

One possible approach in this situation (but only if the stored JSON has a fixed structure) are the following steps:

• Transform the JSON content into tables using two nested OPENJSON() calls with the appropriate explicit schemas.
• Include the new JSON property as column.
• Output the tables as JSON using FOR JSON.

T-SQL:

UPDATE Templates
SET JsonContent = (  
   SELECT 
      Name,
      Options = (
         SELECT [Name], [Value], CAST(0 as bit) AS [IsDeleted]
         FROM OPENJSON(Options) WITH (
            [Name] nvarchar(max) '$.Name',
            [Value] int '$.Value' 
         )
         FOR JSON PATH, INCLUDE_NULL_VALUES
      )
   FROM OPENJSON(@json) WITH (
      Name nvarchar(max) '$.Name',
      Options nvarchar(max) '$.Options' AS JSON
   )
   FOR JSON PATH, INCLUDE_NULL_VALUES
)