CODEWITHSUNDEEP
sql-serverjsonazure-sql-databasesql-server-2016sql-server-json
d.popov
d.popov

Reputation: 4255

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

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

Upvotes: 2

Views: 2725

Answers (1)

d.popov
d.popov

Reputation: 4255

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.

Upvotes: 4

Related Questions