1

As per this JSON Insert into a MySQL table or update if exists, I got answer mysql to insert/update the below json.

"google", 
"20180510", 
"{
   "gmail_page_viewed" : 12000300, 
   "search_page_viewed" : 9898884726423
 }"

into the SCHEMA 

CREATE TABLE TAG_COUNTER (
    account       varchar(36) NOT NULL,
    time_id       INT NOT NULL,
    counters      JSON,
    PRIMARY KEY   (account, time_id)
)

like below

INSERT INTO `TAG_COUNTER`
  (`account`, `time_id`, `counters`)
VALUES
  ('google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": 1}'),
  ('google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": 1}'),
  ('google', '20180511', '{"gmail_page_viewed": 1, "search_page_viewed": 1}'),
  ('google', '20180511', '{"gmail_page_viewed": 1, "search_page_viewed": 1}'),
  ('google', '20180512', '{"gmail_page_viewed": 1, "search_page_viewed": 1}')
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
  );

However it is difficult to retrieve the data from it using group by, since json key is dynamic and not static.

So if i change my json to have static keys (e.g. page_name, page_visits) to the below format, to make retrieval (groupby/orderby) is easy.

"google", 
"20180510", 
'{
   "gmail_page_viewed" : {"page_name" : "gmail_page_viewed", "page_visits" : 12000300}, 
   "search_page_viewed" : {"page_name" : "search_page_viewed", "page_visits" : 12000300}
 }"

But i couldn't figure out how to prepare INSERT ON DUPLICATE KEY UPDATE query. I have tried for more than 6 hours, but no luck, either data is not inserted properly, or not updating the visit count properly. sometime quote gets added into the content.

Could anyone help me on this?

Kanagavelu Sugumar
  • 18,766
  • 20
  • 94
  • 101
  • `ON DUPLICATE KEY UPDATE ` requires a UNIQUE key on your table do you have one? – Raymond Nijland May 14 '18 at 14:38
  • @RaymondNijland Yes I have updated the schema into the question. – Kanagavelu Sugumar May 14 '18 at 14:44
  • I don't understand the whole problem, but the following query can give you some ideas, see [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d2c96eecc3b0ded293f944503b11f9dc). – wchiquito May 14 '18 at 16:02
  • @wchiquito Thanks, i will test confirm you. One thing I see the update only involves page visits, so when new tag come after already record/tag will not update name i guess, but will update you after testing. – Kanagavelu Sugumar May 14 '18 at 18:27
  • @wchiquito Thank you!! and your solution nearly same what i have asked. But when the account and time_id is same and when trying to insert new different tag, it is not updating properly. This is because of already a row is created for that primary key and next row insert/update is failing. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7674491325798342fe371c827566a824 – Kanagavelu Sugumar May 15 '18 at 00:15
  • Try [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3febf68298318a1ea54b78998d549073). – wchiquito May 15 '18 at 11:19
  • @wchiquito As usual it looks what i needed. Thank you!! One doubt how `"page_name": "g_page_viewed"` got added, since it is not part of `JSON_SET`? there is only mention about the `page_visits` after/under `JSON_SET` section. – Kanagavelu Sugumar May 15 '18 at 16:18

0 Answers0