How to update part of a json, using JSON_MODIFY

Question

I am using mssql as DB and storing all details as a json String.

Now i want to update part of a json object.

How to do that?

Below is the json Object.

{"customerName":"mohan","salesForceUrl":"erferf","notes":"ferfer","custId":"3bc5a660-c001-11e9-84a1-11b306ffa283","deleteInd":"N","createdDate":1565944718619,"lastUpdatedDate":null,"deletedDate":null,"feeds":[]}

when i want to update everything except feeds which is an array, how to do that, i am using JPA @Query for this.

@Modifying
    @Query(value="update Customers set configJSON = JSON_MODIFY(configJSON, '$.customerName', 'mohan') where CustomerId = ?1", nativeQuery=true)
    @Transactional
    public void updateCutomer(String custId);

Customers is the table name configJSON is the column name.

Using JSON_MODIFY(configJSON, '$.customerName', 'mohan') is one possible approach. What's wrong with this statement? — Zhorov
– Zhorov, Commented Aug 16, 2019 at 10:52
as i am getting details from the UI as part of customer update info. — mohan
– mohan, Commented Aug 16, 2019 at 11:02
If I understand, you need a T-SQL statement to update the row in a table with the content of the input JSON text, based on "custId" value in this JSON? And, what is your SQL Server version? Thanks. — Zhorov
– Zhorov, Commented Aug 16, 2019 at 11:13
yes,sql server 2016. In the same JSON i am keeping all data, but as i have an UI. the below are part of customer module, so when i want to update the customer module, i need to update all except the feeds[]. Hope you understand the problem. "customerName":"mohan","salesForceUrl":"erferf","notes":"ferfer","custId":"3bc5a660-c001-11e9-84a1-11b306ffa283","deleteInd":"N","createdDate":1565944718619,"lastUpdatedDate":null,"deletedDate":null — mohan
– mohan, Commented Aug 16, 2019 at 11:14

Zhorov · Accepted Answer · 2019-08-16 14:31:26Z

0

One possible approach is to extract the $.feeds part from the current JSON, update the input JSON with this $.feeds part and finally update the table with the modified input JSON:

Table:

CREATE TABLE Customers (
   customerId int,
   configJSON nvarchar(max)
)
INSERT INTO Customers
   (customerId, configJSON)
VALUES
   (1, N'{"customerName":"xxx", "custId":"3bc5a660-c001-11e9-84a1-11b306ffa283", "feeds":[{"a": 1}, {"b": 2}]}')

Statement:

DECLARE @json nvarchar(max) = N'{"customerName":"mohan","salesForceUrl":"erferf","notes":"ferfer","custId":"3bc5a660-c001-11e9-84a1-11b306ffa283","deleteInd":"N","createdDate":1565944718619,"lastUpdatedDate":null,"deletedDate":null,"feeds":[]}'
UPDATE Customers
SET configJSON = JSON_MODIFY(
   @json,
   '$.feeds',
   JSON_QUERY(configJSON, '$.feeds')
)
WHERE customerId = 1

edited Aug 16, 2019 at 14:31

answered Aug 16, 2019 at 11:34

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

8 Comments

mohan Over a year ago

i didnt understand this, can you please explain this? i want something like this... Modifying Query(value="update Customers set configJSON = JSON_MODIFY(configJSON, configJSON, ?1) where CustomerId = ?2", nativeQuery=true) @Transactional public void updateCutomer(String custJson, String custId); where i will get the new Json as well as the custId for which i need to do that ipdate

Zhorov Over a year ago

@mohan I'm not familiar with JPA, but probably the statement should look like this: UPDATE Customers SET configJSON = JSON_MODIFY(?1, '$.feeds', JSON_QUERY(configJSON, '$.feeds')) WHERE CustomerId = ?2.

mohan Over a year ago

@Zhorov...i do have an issue...can you help me?

Zhorov Over a year ago

@mohan What's the issue? Thanks.

mohan Over a year ago

if i have below json, {"customerName":"mohan","custId":"e35273d0-c002-11e9-8188-a1525f580dfd","feeds":[{"feedId":"57f221d0-c310-11e9-8af7-cf1cf42fc72e","feedName":"ccsdcdscsdc","format":"MS Excel","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]},{"feedId":"59bbd360-c312-11e9-8af7-cf1cf42fc72e","feedName":"dfgdfgdfgdfgsdfg","format":"XmlTV","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]}]} if i am passing custId and feedID how to update specific feed with the new feed that in tsql?

|

Collectives™ on Stack Overflow

How to update part of a json, using JSON_MODIFY

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related