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"}

d.popov · Accepted Answer · 2016-08-17 13:01:35Z

4

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.

answered Aug 17, 2016 at 12:16

d.popov

4,2951 gold badge40 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

why do we need coalesce and {} ?