By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();
| VERSION() |
|---|
| 8.0.36 |
DROP TABLE IF EXISTS `TAG_COUNTER`;
CREATE TABLE IF NOT EXISTS `TAG_COUNTER` (
`account` VARCHAR(36) NOT NULL,
`time_id` INT NOT NULL,
`counters` JSON,
PRIMARY KEY (`account`, `time_id`)
);
INSERT INTO `TAG_COUNTER`
(`account`, `time_id`, `counters`)
VALUES
('google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": 50}'),
('google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": 50}'),
('google', '20180511', '{"gmail_page_viewed": 1, "search_page_viewed": 100}'),
('google', '20180511', '{"gmail_page_viewed": 1, "search_page_viewed": 100}'),
('google', '20180511', '{"gmail_page_viewed": 1, "search_page_viewed": 100}')
ON DUPLICATE KEY UPDATE `counters` =
JSON_SET(`counters`,
'$."gmail_page_viewed"',
IFNULL(`counters` ->> '$."gmail_page_viewed"', 0) + 1,
'$."search_page_viewed"',
IFNULL(`counters` ->> '$."search_page_viewed"', 0) + 1
);
Records: 5 Duplicates: 3 Warnings: 0
SELECT
`account`,
`time_id`,
`counters`
FROM
`TAG_COUNTER`;
| account | time_id | counters |
|---|---|---|
| 20180510 | 7b22676d61696c5f706167655f766965776564223a20322e302c20227365617263685f706167655f766965776564223a2035312e307d | |
| 20180511 | 7b22676d61696c5f706167655f766965776564223a20332e302c20227365617263685f706167655f766965776564223a203130322e307d |