By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `table1` (
`id` int(11) NOT NULL,
`tableType` varchar(45) DEFAULT NULL,
`jkey` varchar(45) DEFAULT NULL,
`jval` json NOT NULL,
PRIMARY KEY (`id`)
)
SHOW COLUMNS FROM `table1`;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | 696e74 | NO | PRI | null | |
| tableType | 7661726368617228343529 | YES | null | ||
| jkey | 7661726368617228343529 | YES | null | ||
| jval | 6a736f6e | NO | null |
ALTER TABLE `table1` ADD COLUMN `group_virtual` VARCHAR(250) GENERATED ALWAYS AS (`jval` ->> '$.group') NOT NULL AFTER `jval`;
Records: 0 Duplicates: 0 Warnings: 0
SHOW COLUMNS FROM `table1`;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | 696e74 | NO | PRI | null | |
| tableType | 7661726368617228343529 | YES | null | ||
| jkey | 7661726368617228343529 | YES | null | ||
| jval | 6a736f6e | NO | null | ||
| group_virtual | 766172636861722832353029 | NO | null | VIRTUAL GENERATED |
ALTER TABLE `table1` ADD COLUMN `user_virtual` VARCHAR(250) GENERATED ALWAYS AS (`jval` ->> '$.user') NOT NULL AFTER `jval`;
Records: 0 Duplicates: 0 Warnings: 0
SHOW COLUMNS FROM `table1`;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| id | 696e74 | NO | PRI | null | |
| tableType | 7661726368617228343529 | YES | null | ||
| jkey | 7661726368617228343529 | YES | null | ||
| jval | 6a736f6e | NO | null | ||
| user_virtual | 766172636861722832353029 | NO | null | VIRTUAL GENERATED | |
| group_virtual | 766172636861722832353029 | NO | null | VIRTUAL GENERATED |