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?

Does the stored JSON always have this fixed structure? And what is the SQL Server version? — Zhorov
– Zhorov, Commented Jan 15 at 6:45

Zhorov · Accepted Answer · 2025-03-11 09:23:12Z

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
)

As a note, on Azure SQL Database (and probably on SQL Server 2025), you may use the JSON_ARRAYAGG() function to construct a JSON array from an aggregation of SQL data or columns.

Bart McEndree · Accepted Answer · 2025-01-14 13:24:56Z

0

Using the example from How to update insert JSON property in an array of object with T-SQL I applied your scenario:

SELECT @json = JSON_MODIFY(@json, CONCAT('$.Options[', [key], '].IsDeleted'), 'false')
FROM OPENJSON(@json, '$.Options')

answered Jan 14 at 13:24

Bart McEndree

3,6041 gold badge12 silver badges20 bronze badges

1 Comment

alex Jan 14 at 13:40

This works for single object, not for array of objects. I need to iterate each object in the array and do execute the query you sent.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related