将json插入mysql表或更新(如果存在)

dojqjjoe  于 2021-06-25  发布在  Mysql
关注(0)|答案(1)|浏览(382)

这是insert到mysql表或update(如果存在)的继续,但这次我想更新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}'

实际上,我总是想更新(增加计数器)这个表。但是每天的开始时间\u id(yyyymmdd)是新的,因此counter json没有counter键(例如。 gmail_page_viewed ).
所以我想要的解决方案是用 value = 1 当它不存在时。例如。 {"gmail_page_viewed" : 1} 如果计数器(例如。 gmail_page_viewed )密钥存在,然后递增 value = value + 1 因此,如果json计数器存在,请帮助我使用update进行insert查询。
更新\u 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}'

你知道为什么这个新的计数器搜索页面被设置为空。。?
更新\u 2(信息:当数字用作键时,在键周围使用双引号)
我也遇到了下面的问题,并从这个答案中解决了问题
MySQL5.7错误3143(42000):json路径表达式无效。错误在字符位置3附近
更新\u 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;

更新\u 4(问题:)
我想看看每一次的趋势页(小时/天/月/年)从下面的查询我可以得到每一次的计数,但只有当我知道的关键(gmail\u页\u查看)。

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')
iszxjhcz

iszxjhcz1#

以下脚本可用于满足您的需求:

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
  );

见小提琴。

相关问题