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.

Java Devil · Accepted Answer · 2019-03-18 23:17:51Z

18

I think you want this syntax:

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

edited Mar 18, 2019 at 23:17

answered Mar 18, 2019 at 23:09

Java Devil

11.1k7 gold badges37 silver badges50 bronze badges

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

Comments

Mukesh Arora · Accepted Answer · 2019-03-19 04:24:36Z

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

Gauravsa · Accepted Answer · 2019-03-18 23:09:44Z

1

In your case, you need to use append with JSON_Modify. Something like this:

UPDATE <TableName>
SET <jsonColumnName> = JSON_MODIFY(<jsonColumnName>, 'append $', JSON_QUERY(N'{"City":"Pune"}'))
WHERE UserId=1;

answered Mar 18, 2019 at 23:09

Gauravsa

6,6382 gold badges25 silver badges33 bronze badges

1 Comment

Anil Over a year ago

Thanks for the answer but I already tried this but didn't work. I guess append works only with array. "Java Devil" 's answer worked.

Evidence Ekanem · Accepted Answer · 2023-06-22 15:02:44Z

0

I want to add that Gauravsa's answer is helpful when storing an object to the JSON property otherwise it would be stored as a deserialized object.

An example usage is:

UPDATE [TableName]
SET [jsonColumnName]=JSON_MODIFY([jsonColumnName],'$.secondaryActionColor', JSON_QUERY(N'{"alpha":1,"hex":"#FFC600","hexa":"#FFC600FF","hsla":{"h":46.588235294117645,"s":1,"l":0.5,"a":1},"hsva":{"h":46.588235294117645,"s":1,"v":1,"a":1},"hue":46.588235294117645,"rgba":{"r":255,"g":198,"b":0,"a":1}}'))
WHERE UserId=1;

answered Jun 22, 2023 at 15:02

Evidence Ekanem

1491 silver badge8 bronze badges

Collectives™ on Stack Overflow

How to add property to JSON object in SQL Server column

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related