Append JSON values to a JSON array in SQL Server?

Question

I am trying to append some JSON values to a JSON array in SQL Server.

Here is what I have tried from other similar questions asked.

DECLARE @features NVARCHAR(500)='{"Name": "Feature1","IsEnabled": false},{"Name": "Feature2","IsEnabled": true},{"Name": "Feature3","IsEnabled": false}'

UPDATE JsonTable
   SET 
    Content = JSON_MODIFY (
      Content, 
      'append $.Features', 
      JSON_QUERY(@features)
      )

But it is only appending "Name": "Feature1","IsEnabled": false and not the rest.

My current Content looks something like this

{
  "Features": [
    {
      "Name": "Feature0",
      "IsEnabled": true
    }
  ],
  "SpecialFeatures": [
    {
      "Name": "SFeature0",
      "IsEnabled": false
    }
  ]
}

and the expected results is like below

{
  "Features": [
    {
      "Name": "Feature0",
      "IsEnabled": true
    },
    {
      "Name": "Feature1",
      "IsEnabled": false
    },
    {
      "Name": "Feature2",
      "IsEnabled": true
    },
    {
      "Name": "Feature3",
      "IsEnabled": false
    }
  ],
  "SpecialFeatures": [
    {
      "Name": "SFeature0",
      "IsEnabled": false
    }
  ]
}

Can anyone help on the same?

Answer 1

JSON_MODIFY will not append multiple values to an array even if you use append , it's only designed for single values or objects. Also your JSON is not valid as it's missing the [] array delimiters.

You can instead rebuild your JSON in a subquery.

DECLARE @features NVARCHAR(500)='[{"Name": "Feature1","IsEnabled": false},{"Name": "Feature2","IsEnabled": true},{"Name": "Feature3","IsEnabled": false}]'

UPDATE JsonTable
   SET 
    Content = JSON_MODIFY (
      Content, 
      '$.Features', 
        (
          SELECT j.*
          FROM (
              SELECT j1.*
              FROM OPENJSON(Content, '$.Features')
                WITH (
                  Name nvarchar(100),
                  IsEnabled bit
                ) j1
              UNION ALL
              SELECT j2.*
              FROM OPENJSON(@features)
                WITH (
                  Name nvarchar(100),
                  IsEnabled bit
                ) j2
          ) j
          FOR JSON PATH
        )
      )

SQL Fiddle

In Azure SQL and SQL Server 2025 you can use JSON_ARRAYAGG

DECLARE @features NVARCHAR(500)='[{"Name": "Feature1","IsEnabled": false},{"Name": "Feature2","IsEnabled": true},{"Name": "Feature3","IsEnabled": false}]'

UPDATE JsonTable
   SET 
    Content = JSON_MODIFY (
      Content, 
      '$.Features', 
        (
          SELECT JSON_ARRAYAGG(JSON_QUERY(j.value) ORDER BY Ordering, CAST([key] AS int))
          FROM (
              SELECT *, 1 AS Ordering
              FROM OPENJSON(Content, '$.Features')
              UNION ALL
              SELECT *, 2
              FROM OPENJSON(@features)
          ) j
        )
      )