JSON Insert into a MySQL table or update if exists

Question

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

Mysql 5.7 ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 3

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

I'm not sure I really understand your requirement, however, the following script can give you some ideas, see [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3f7cfe549dcfdf77e2dc4d915213b4aa). — wchiquito, May 10 '18 at 13:41
@wchiquito That is awesome! One question you have updated the `gmail_page_viewed` but not updated the `search_page_viewed`? How to update both in this case ? — Kanagavelu Sugumar, May 10 '18 at 14:14
See updated [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4246d5b1f3397cb2914062fd63929060). — wchiquito, May 10 '18 at 14:21
@wchiquito Excellent! That is what i want. Thank you! very much. Could you please add this as answer below, so that i can accept it? — Kanagavelu Sugumar, May 10 '18 at 14:23
See updated [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=47c527f140cd7a519b43cd63b09dd49f). — wchiquito, May 10 '18 at 20:20
`... SUM(IFNULL(counters->>'$."gmail_page_viewed"', 0)) ...` and `... IFNULL(counters->>'$."gmail_page_viewed"', 0) > 0 ...`. — wchiquito, May 11 '18 at 11:39
@wchiquito you have helped me a lot, last one question, how to get trending page (which has more count) for day/month/year. The problem is in group by i have to know the key to get the count, but is it possible to get the trending without knowing key. Let me update the question. — Kanagavelu Sugumar, May 11 '18 at 11:44
Maybe you need a dynamic query, see [13.5 Prepared SQL Statement Syntax](https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html). — wchiquito, May 11 '18 at 11:57
@wchiquito This is similar question https://stackoverflow.com/questions/41650221/mysql-sum-json-values-grouped-by-json-keys and i think i have to go from `{"key1" : "value1", "key2":"value2" }` to `[{"name" : "key1", "value":"value1" }, {"name" : "key2", "value":"value2" }, ...]`, so that i can add `name` into my `group by` along with time ?? — Kanagavelu Sugumar, May 11 '18 at 12:01
@wchiquito asked update_4 as a separate question https://stackoverflow.com/questions/50297616/mysql-json-based-trending-tags-implementation with bounty..? Please help. — Kanagavelu Sugumar, May 14 '18 at 06:55

wchiquito · Accepted Answer · 2018-05-11T11:29:02.957

1

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

See dbfiddle.

edited May 11 '18 at 11:29

answered May 10 '18 at 14:27

wchiquito

14,738
2
30
42

I see different problem when following the solutions. Could you please help on the update ..? – Kanagavelu Sugumar May 10 '18 at 19:43
@KanagaveluSugumar: Remember to adjust the script according to your needs, see updated [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=47c527f140cd7a519b43cd63b09dd49f). – wchiquito May 10 '18 at 20:07

JSON Insert into a MySQL table or update if exists

1 Answers1

Linked