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"
}
]';
with cte as (
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'
)
update tablename cross join cte
set tablename.json_column = json_set(json_column, concat('$[', cte.ord-1, '].Value'), 'CALI')
where cte.id = tablename.id;
select id, json_pretty(json_column) from tablename;
Rows matched: 1 Changed: 1 Warnings: 0
| id | json_pretty(json_column) |
|---|---|
| 1 | [ { "Id": "PhoneNumber", "Name": "Number", "Value": "+393123456789" }, { "Id": "FirstName", "Name": "Name", "Value": "John" }, { "Id": "CityID", "Name": "City", "Value": "CALI" } ] |