UPDATE command in a JSON

user_11504053 0
Viewed 95

I'm trying to update a column in my table, but that column stores a JSON Object, but for that I need to do a where with a JSON as well.

This is how the data is stored in my RG column:

{
   "Id":"00000",
   "Number":"12345678",
   "Emitter":"SSP/SP",
   "Uf":"SP",
   "Emission":"2020-03-04T03:00:00Z"
}

First I would like to know what is wrong with this SELECT statement:

select * from People where JSON_VALUE (Rg, '$.Uf') LIKE '%35%'

And my UPDATE command I tried to do as follows:

update People set Rg = JSON_MODIFY (Rg, '$.Uf', 'SP') where JSON_VALUE (Rg, '$.Uf') LIKE '%35%'

Could someone help me make the select and this update, please? And what's wrong with my commands? I couldn't understand how to do these two commands when a JSON OBJECT is involved.

sql-update sql-server sql json
1 Answers

Both your SELECT query and UPDATE statements are fine. I tried below with test setup. I did not face any issue.

Test Setup

CREATE TABLE Test
(
  a int,
  rg NVARCHAR(MAX)
 )

 INSERT INTO Test
 values (1, N'{
   "Id":"00000",
   "Number":"12345678",
   "Emitter":"SSP/SP",
   "Uf":"SP",
   "Emission":"2020-03-04T03:00:00Z"
}');

Running Select Query

select * from Test where JSON_VALUE (Rg, '$.Uf') LIKE '%SP%'

+---+--------------------------------------------------------------------------------------------+
| a |                                                   rg                                                    |
+---+--------------------------------------------------------------------------------------------+
| 1 | { "Id":"00000", "Number":"12345678", "Emitter":"SSP/SP", "Uf":"SP", 
"Emission":"2020-03-04T03:00:00Z" } |
+---+--------------------------------------------------------------------------------------------+

Updating and Selecting to verify whether update was successful

update Test 
set rg = JSON_MODIFY(rg, '$.Uf', 'MP') 
where JSON_VALUE(rg, '$.Uf') LIKE '%SP%'

select * from Test;

+---+--------------------------------------------------------------------------------------------+
| a |                                                   rg                                                    |
+---+--------------------------------------------------------------------------------------------+
| 1 | { "Id":"00000", "Number":"12345678", "Emitter":"SSP/SP", "Uf":"MP", 
"Emission":"2020-03-04T03:00:00Z" } |
+---+--------------------------------------------------------------------------------------------+
user_6349350
Related