How to add property to JSON object in SQL Server column

Question

One of my table has JSON data in a SQL Server database. I need to add new property to existing JSON.

Example of JSON data in Table:

{ "FirstName": "John" }

What I need is to add another property to this column.

{ "FirstName": "Jonh","City":"Pune" }

I tried to use JSON_MODIFY function but it is throwing error. Does this work only with array?

Code which I tried:

update <TableName> 
set <jsonColumnName> = JSON_MODIFY(<jsonColumnName>, '$.', JSON_QUERY(N'{ "City":"Pune" }'))
where UserId = 1

Error message:

JSON path is not properly formatted. Unexpected character '.' is found at position 2.

Answer 1

I think you want this syntax:

UPDATE <TableName>
SET <jsonColumnName>=JSON_MODIFY(<jsonColumnName>,'$.City','Pune')
WHERE UserId=1

Answer 2

Example to Add, Update and Delete a Property From JSON

Sample Table and Data

declare @Table table
    (JsonColumn varchar(max))

insert into @Table
values
('{"FirstName": "John"}')

select * from  @Table

Insert New Property

UPDATE @Table
SET JsonColumn=JSON_MODIFY(JsonColumn,'$.City','Pune')

select * from  @Table

Update Existing Property

UPDATE @Table
SET JsonColumn=JSON_MODIFY(JsonColumn,'$.City','Delhi')

select * from  @Table

Update Multiple Existing Properties

UPDATE @Table
SET JsonColumn= JSON_MODIFY(JSON_MODIFY(JsonColumn,'$.City','Delhi'), '$.FirstName', 'Mukesh')

select * from  @Table

Delete Existing Property

UPDATE @Table
SET JsonColumn=JSON_MODIFY(JsonColumn,'$.City',null)

select * from  @Table