JSON插入MySQL表或更新(如果存在) [英] JSON Insert into a MySQL table or update if exists

问题描述

这是插入MySQL表或更新的延续如果存在，但是这次我想更新mysql中的json条目

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

以下是架构:

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)是新的，因此counter json没有计数器key(例如gmail_page_viewed).

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

所以我想要的解决方案是在不存在的情况下用
value = 1创建新的json键列.例如{"gmail_page_viewed" : 1}

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}

如果存在计数器键(例如gmail_page_viewed)，则增加
value = value + 1

if counter (e.g. gmail_page_viewed) key exists then increment the
value = value + 1

因此，如果json计数器存在，请帮助我进行带有更新的插入查询.

So help me on a insert query with update if json counter exists.

UPDATE_1(信息:使用json时始终使用IFNULL)

UPDATE_1 (INFO: Always use IFNULL when working with json)

我使用了@wchiquito建议的解决方案，但看到了以下问题.

I have used the solution suggested by @wchiquito but seeing the below issue.

第一次(架构创建后)；尝试下面的sql

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

并得到以下内容.

'google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": null}'  

您知道为什么这个新计数器search_page_viewed设置为NULL吗??

UPDATE_2(信息:将数字用作键时，在键周围使用双引号)

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

​​ Mysql 5.7错误3143(42000):无效的JSON路径表达式.错误出现在字符位置3周围

UPDATE_3(信息:分组依据，请确保WHERE处带有"NOT NULL"或> 0"

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(问题:)

UPDATE_4 (Question: )

我想查看每个时间的趋势页面(小时/天/月/年) 从下面的查询中，我可以获取每次的计数，但前提是我知道密钥(gmail_page_viewed).

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

请参见 dbfiddle .

这篇关于JSON插入MySQL表或更新(如果存在)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！