How to add property to JSON object in SQL Server column

qacovj5a 于 2023-06-28 发布在 SQL Server

关注(0)|答案(4)|浏览(143)

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.

sql-server

来源：https://stackoverflow.com/questions/55231281/how-to-add-property-to-json-object-in-sql-server-column

4条答案

按热度按时间

ffx8fchx1#

I think you want this syntax:

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

赞(0）回复(0）举报 2023-06-28

hl0ma9xz2#

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

赞(0）回复(0）举报 2023-06-28

ht4b089n3#

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;

赞(0）回复(0）举报 2023-06-28

4xrmg8kj4#

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;

赞(0）回复(0）举报 2023-06-28

我来回答

How to add property to JSON object in SQL Server column

4条答案

相关问题

热门标签

最新问答