By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tablename (
id serial primary key,
json_column varchar(1000)
);
insert into tablename set id = 1, json_column = '[
{
"Name":"Number",
"Id":"PhoneNumber",
"Value":"+393123456789"
},
{
"Name":"Name",
"Id":"FirstName",
"Value":"John"
},
{
"Name":"City",
"Id":"CityID",
"Value":"NYC"
}
]';
update tablename
cross join (
select ID, j.* from tablename
cross join json_table(JSON_COLUMN, '$[*]' columns(
ord for ordinality,
Value varchar(100) path '$.Value')
) as j
where j.Value = 'NYC'
) as t
set tablename.json_column = json_set(json_column, concat('$[', t.ord-1, '].Value'), 'CALI');
select id, json_column from tablename;
Rows matched: 1 Changed: 1 Warnings: 0
| id | json_column |
|---|---|
| 1 | [{"Name": "Number", "Id": "PhoneNumber", "Value": "+393123456789"}, {"Name": "Name", "Id": "FirstName", "Value": "John"}, {"Name": "City", "Id": "CityID", "Value": "CALI"}] |