如何统计JSON格式文本列中音乐风格的出现次数？

你当前的LIKE查询会匹配整个elements字段中的所有字符串，容易出现误统计（比如其他节点的文本刚好包含风格名称）。以下是基于MySQL JSON函数的精准统计方案：

适用MySQL 8.0+（推荐） #

利用JSON_TABLE将JSON对象中的音乐风格拆分为单独行，再分组统计：

SELECT style, COUNT(*) AS 出现次数
FROM rwm_zoo_item,
     JSON_TABLE(
         -- 精准定位到目标节点下的option对象
         JSON_EXTRACT(elements, '$."2276b834-35d0-4ef0-a747-106ea454e478".option'),
         '$.*' COLUMNS(style VARCHAR(100) PATH '$')
     ) AS styles
GROUP BY style
ORDER BY 出现次数 DESC;

逻辑说明： #

1. JSON_EXTRACT(elements, '$."2276b834-35d0-4ef0-a747-106ea454e478".option')：精准提取目标ID节点下的option对象（该对象存储所有关联的音乐风格）。
2. JSON_TABLE：将option这个JSON对象的所有值拆分成独立行，每行对应一个音乐风格。
3. 最后按风格分组，统计每个风格的出现次数。

适用MySQL 5.7（无JSON_TABLE支持） #

如果你的MySQL版本低于8.0，可以通过UNION ALL逐个提取option中的风格值：

SELECT style, COUNT(*) AS 出现次数
FROM (
    -- 提取option下键为0的风格
    SELECT JSON_UNQUOTE(JSON_EXTRACT(elements, '$."2276b834-35d0-4ef0-a747-106ea454e478".option."0"')) AS style
    FROM rwm_zoo_item
    WHERE JSON_EXTRACT(elements, '$."2276b834-35d0-4ef0-a747-106ea454e478".option."0"') IS NOT NULL
    UNION ALL
    -- 提取option下键为1的风格
    SELECT JSON_UNQUOTE(JSON_EXTRACT(elements, '$."2276b834-35d0-4ef0-a747-106ea454e478".option."1"')) AS style
    FROM rwm_zoo_item
    WHERE JSON_EXTRACT(elements, '$."2276b834-35d0-4ef0-a747-106ea454e478".option."1"') IS NOT NULL
    -- 如果option下有更多键，继续添加对应的UNION ALL语句
) AS all_styles
WHERE style IS NOT NULL
GROUP BY style
ORDER BY 出现次数 DESC;

注意事项： #

• 此方法需要明确option对象中的键名（如示例中的0、1），如果键名不固定或数量较多，维护成本较高。

内容的提问来源于stack exchange，提问作者Sébastien Merour