This is continuation of Insert into a MySQL table or update if exists, but this time i want to update the json entry in mysql
Below is the schema:
CREATE TABLE TAG_COUNTER (
account varchar(36) NOT NULL,
time_id INT NOT NULL,
counters JSON,
PRIMARY KEY (account, time_id)
)
example data's are like below.
'google', '20180510', '{"gmail_page_viewed" : 12000300, "search_page_viewed" : 9898884726423}'
Actually i always want to update (increment the counter ) of this table. but every day start time_id(yyyyMMdd) is new hence counter json dont have the counter key (e.g.gmail_page_viewed).
So the solution i want is create new json key column with
value = 1 when it is not exists. e.g. {"gmail_page_viewed" : 1}
if counter (e.g. gmail_page_viewed) key exists then increment the
value = value + 1
So help me on a insert query with update if json counter exists.
UPDATE_1 (INFO: Always use IFNULL when working with json)
I have used the solution suggested by @wchiquito but seeing the below issue.
Very first time (after schema creation); tried the below sql
INSERT INTO `TAG_COUNTER`
(`partner_id`, `time_id`, `counters`)
VALUES
('google', '20180510', '{"gmail_page_viewed": 1}')
ON DUPLICATE KEY UPDATE `counters` =
JSON_SET(`counters`,
'$.gmail_page_viewed',
JSON_EXTRACT(`counters`,
'$.gmail_page_viewed') + 1
);
and got the below response; which is correct as expected.
'google', '20180510', '{"gmail_page_viewed": 1}'
Then tried with different counter in the next query,
INSERT INTO `TAG_COUNTER`
(`account`, `time_id`, `counters`)
VALUES
('google', '20180510', '{"search_page_viewed": 1}')
ON DUPLICATE KEY UPDATE `counters` =
JSON_SET(`counters`,
'$.search_page_viewed',
JSON_EXTRACT(`counters`,
'$.search_page_viewed') + 1
);
and got the below.
'google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": null}'
Any idea why this new counter search_page_viewed is set with NULL ..?
UPDATE_2 (INFO: Use double quote around key when number is used as key)
I have faced below issue as well, and solved the it from that answer
UPDATE_3 (Info: Group by, ensure WHERE with "NOT NULL" or "> 0"
Getting the count per time frame (e.g. by day, month, year)
SELECT
SUBSTRING(time_id, 1, 6) AS month,
SUM(counters->>'$.gmail_page_viewed')
FROM TAG_COUNTER
WHERE counters->>'$.gmail_page_viewed' > 0
GROUP BY month;
UPDATE_4 (Question: )
I would like see the trending page per time (hour/day/month/year)
From the below query i can get the count per time but only if i know the key (gmail_page_viewed).
SELECT
SUBSTRING(time_id, 1, 6) AS month,
SUM(counters->>'$.gmail_page_viewed')
FROM TAG_COUNTER
WHERE counters->>'$.gmail_page_viewed' > 0
GROUP BY month;
But what i want is how do i know the overall trending of the page count per time? So I need something like below, Please help.
group by SUBSTRING(time_id, 1, 6) AS month, KEY
order by SUM(counters->>'$.KEY')
解决方案
The following script can be useful for your requirement:
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
);