mysql json update,JSON插入MySQL表或更新（如果存在）

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

);