By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Metadata (id int,meta json
, cycle VARCHAR(255) AS (JSON_UNQUOTE(meta->>'$.cycle'))
);
insert into Metadata (id,meta)values
(1,'{"idx":"12","cycle":"cycle1"}')
;
select id,cast(meta as json)meta,cycle
from Metadata;
| id | meta | cycle |
|---|---|---|
| 1 | {"idx": "12", "cycle": "cycle1"} | cycle1 |
UPDATE Metadata
SET meta=json_set(meta,'$.cycle','MONTHLY')
Rows matched: 1 Changed: 1 Warnings: 0
select id,cast(meta as json)meta,cycle
from Metadata;
| id | meta | cycle |
|---|---|---|
| 1 | {"idx": "12", "cycle": "MONTHLY"} | MONTHLY |