Append Values to JSON object in SQL 2016 / Modify JSON object (SQL Server JSON Data)

Question

I have a JSON column in SQL2016 database, and I want to add a key-value pair to the root of the JSON object.

The content of the JSON column should be altered from

{"Info1":"Value1"}

to

{"Info1":"Value1", "Info2":"Value2"}

Answer 1

With SQL Server 2016, it can be done using the built-in functions to manipulate JSON data. The following function will return the modified JSON data:

JSON_MODIFY(JsonColumn, '$.Info2', 'Value2')

The expression can be used in normal UPDATE statement:

UPDATE Table1
SET JsonColumn = JSON_MODIFY(JsonColumn, '$.Info2', 'Value2')

The NULL values in JsonColumn will be updated to { "Info2":"Value2"}.

If JsonColumn contains another value for Info2 key, it will be overwritten.