MySQL中JSON类型计数器的插入更新及趋势查询问题

Got it, let's tackle your MySQL JSON counter problems step by step—this builds directly on the "insert or update" logic you're already working with, but adds JSON-specific handling for your use case.

Your core requirement is to either create a new counter key with value 1 if it doesn't exist, or increment it by 1 if it does—all while handling the "insert if not exists, update if exists" logic for the account + time_id primary key.

Here's the solid INSERT ... ON DUPLICATE KEY UPDATE statement to make this work:

INSERT INTO TAG_COUNTER (account, time_id, counters)
VALUES ('google', 20180510, JSON_OBJECT('gmail_page_viewed', 1))
ON DUPLICATE KEY UPDATE
counters = JSON_SET(
    -- Handle cases where counters might be NULL (e.g., old records without the field)
    IFNULL(counters, JSON_OBJECT()),
    '$.gmail_page_viewed',
    -- If the key doesn't exist, JSON_EXTRACT returns NULL—COALESCE turns that to 0 before adding 1
    COALESCE(JSON_EXTRACT(counters, '$.gmail_page_viewed'), 0) + 1
);

Breakdown: #

• IFNULL(counters, JSON_OBJECT()): Ensures we're always working with a valid JSON object, even if the counters field was NULL in an existing record.
• COALESCE(..., 0): Fixes the "key doesn't exist" scenario by treating missing keys as having a value of 0, so incrementing gives us 1 instead of NULL.
• JSON_SET: Updates the key if it exists, or creates it if it doesn't—perfect for our needs.

If you're seeing NULL values when adding a new counter key, the root cause is almost always missing handling for NULL returns from JSON_EXTRACT.

What Goes Wrong: #

If you run something like this (without COALESCE):

-- ❌ BAD: Causes NULL when key doesn't exist
ON DUPLICATE KEY UPDATE
counters = JSON_SET(counters, '$.search_page_viewed', JSON_EXTRACT(counters, '$.search_page_viewed') + 1);

When the key search_page_viewed doesn't exist, JSON_EXTRACT returns NULL. Adding 1 to NULL still gives NULL, which gets stored as the new key's value.

The Fix: #

Use the same COALESCE pattern from the working insert/update statement above. Here's the corrected version for the search_page_viewed key:

-- ✅ GOOD: Guarantees a valid numeric value
ON DUPLICATE KEY UPDATE
counters = JSON_SET(
    IFNULL(counters, JSON_OBJECT()),
    '$.search_page_viewed',
    COALESCE(JSON_EXTRACT(counters, '$.search_page_viewed'), 0) + 1
);

To get page view trends by day/month/year without specifying individual JSON keys, we need to "unpack" the JSON object into rows using MySQL's JSON_TABLE function. This lets us group and aggregate all counter keys dynamically.

Query for Monthly Aggregations: #

SELECT
    -- Convert the integer time_id (YYYYMMDD) to a readable month format
    DATE_FORMAT(STR_TO_DATE(CAST(time_id AS CHAR), '%Y%m%d'), '%Y-%m') AS reporting_month,
    counter_key,
    SUM(CAST(counter_value AS UNSIGNED)) AS total_page_views
FROM (
    -- Inner query: Unpack each JSON key-value pair into separate rows
    SELECT
        time_id,
        JSON_UNQUOTE(key_name) AS counter_key,
        JSON_EXTRACT(counters, CONCAT('$.', JSON_UNQUOTE(key_name))) AS counter_value
    FROM TAG_COUNTER,
         -- Generate a row for each key in the counters JSON object
         JSON_TABLE(JSON_KEYS(counters), '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')) AS key_rows
) AS unpacked_counters
GROUP BY reporting_month, counter_key
ORDER BY reporting_month DESC, total_page_views DESC;

Breakdown: #

1. Unpacking JSON: The inner query uses JSON_TABLE with JSON_KEYS to create a row for every key in the counters object. We then fetch the corresponding value using JSON_EXTRACT.
2. Date Conversion: Converts the integer time_id (e.g., 20180510) to a date, then formats it as a month string (e.g., 2018-05) for grouping.
3. Aggregation: Groups by month and counter key, sums up the values, and sorts to show the most recent months and highest views first.

Adjusting for Day/Year: #

• For daily trends: Change DATE_FORMAT(..., '%Y-%m-%d') instead of %Y-%m.
• For yearly trends: Use DATE_FORMAT(..., '%Y').

内容的提问来源于stack exchange，提问作者Kanagavelu Sugumar