How to add a new root element to a json string in t-sql

user_16496841 0
Viewed 383

I'm stumped on this for whatever reason. How do I add a new root element to the below json?

{
   "Test1":[{"TestValue1":"value1","TestValue2":"value2"}],
   "Test2":[{"TestValue1":"value1","TestValue2":"value2"}],
   "Test3":[{"TestValue1":"value1","TestValue2":"value2"}]
}

I'd like to add "Test4":[{"TestValue1":"value1","TestValue2":"value2"}]

I can read the data from a sql column with openjson and can update a property within one of the elements with json_modify but can't figure out how to add a full new element.

Thanks for any help, Kevin

tsql sql-server sql json
1 Answers

You can use JSON_MODIFY() to add it:

JSON_MODIFY ( expression , path , newValue )

Use append in the path:

append - Optional modifier that specifies that the new value should be appended to the array referenced by <json path>.

DECLARE @jsondata NVARCHAR(MAX)

SET @jsondata = '
{
   "Test1":[{"TestValue1":"value1","TestValue2":"value2"}],
   "Test2":[{"TestValue1":"value1","TestValue2":"value2"}],
   "Test3":[{"TestValue1":"value1","TestValue2":"value2"}]
}
'

SET @jsondata = JSON_MODIFY(@jsondata, 'append $.Test4', JSON_QUERY('{"TestValue1":"value1","TestValue2":"value2"}'))

SELECT @jsondata

To avoid automatic escaping, provide newValue by using the JSON_QUERY function. JSON_MODIFY knows that the value returned by JSON_MODIFY is properly formatted JSON, so it doesn't escape the value.

Which gives you the following results:

{
  "Test1": [
    {
      "TestValue1": "value1",
      "TestValue2": "value2"
    }
  ],
  "Test2": [
    {
      "TestValue1": "value1",
      "TestValue2": "value2"
    }
  ],
  "Test3": [
    {
      "TestValue1": "value1",
      "TestValue2": "value2"
    }
  ],
  "Test4": [
    {
      "TestValue1": "value1",
      "TestValue2": "value2"
    }
  ]
}
user_103765370
Related