这是继续插入 MySQL 表或更新(如果存在),但是这次我想更新mysql中的json条目
下面是架构:
CREATE TABLE TAG_COUNTER (
account varchar(36) NOT NULL,
time_id INT NOT NULL,
counters JSON,
PRIMARY KEY (account, time_id)
)
示例数据如下所示。
'google', '20180510', '{"gmail_page_viewed" : 12000300, "search_page_viewed" : 9898884726423}'
实际上我总是想更新(增加计数器)这个表。但每天的开始 time_id(yyyyMMdd) 都是新的,因此计数器 json 没有计数器键(例如gmail_page_viewed
).
所以我想要的解决方案是创建新的 json 键列
value = 1
当它不存在时。例如{"gmail_page_viewed" : 1}
如果计数器(例如gmail_page_viewed
) 键存在然后递增
value = value + 1
因此,如果 json 计数器存在,请帮助我进行插入查询和更新。
UPDATE_1(信息:使用 json 时始终使用 IFNULL)
我已使用@wchiquito 建议的解决方案,但看到以下问题。
第一次(在创建模式之后);尝试了下面的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
);
并得到以下答复;正如预期的那样是正确的。
'google', '20180510', '{"gmail_page_viewed": 1}'
然后在下一个查询中尝试使用不同的计数器,
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
);
并得到以下内容。
'google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": null}'
知道为什么这个新计数器 search_page_viewed 设置为 NULL ..?
UPDATE_2(信息:当数字用作键时,在键周围使用双引号)
我也遇到了以下问题,并从该答案中解决了它
Mysql 5.7 错误 3143 (42000):无效的 JSON 路径表达式。错误发生在字符位置 3 附近
UPDATE_3(信息:分组依据,确保 WHERE 为“NOT NULL”或“> 0”
获取每个时间范围的计数(例如按日、月、年)
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(问题:)
我想查看每次的趋势页面(小时/天/月/年)
从下面的查询中,我可以获得每次的计数,但前提是我知道密钥(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;
但我想要的是如何知道每次页数的总体趋势?所以我需要像下面这样的东西,请帮忙。
group by SUBSTRING(time_id, 1, 6) AS month, KEY
order by SUM(counters->>'$.KEY')