MySQL中计算JSON数组指定action_type的value总和求助

嗨，我来帮你搞定这个问题～你现在需要从JSON字段ad_insights里，把actions数组中action_type为post_reaction的所有value加起来，对吧？先说说你之前语句的问题：你写的语句只是提取了所有action_type的值，既没过滤出你要的类型，也没做求和计算，所以自然得不到预期的100啦。

下面分两种场景给你解决方案，你根据自己的MySQL版本和JSON结构选就行：

方案一：MySQL 8.0+（推荐，更简洁） #

MySQL 8.0及以上支持JSON_TABLE函数，能把JSON数组直接转换成关系型表结构，方便我们做过滤和聚合。

假设你的ad_insights字段结构是这样的（对应你之前语句里的$[0].actions，也就是ad_insights本身是一个JSON数组，第一个元素里包含actions数组）：

[
  {
    "actions": [
      {"action_type": "post_reaction", "value": "60"},
      {"action_type": "click", "value": "20"},
      {"action_type": "post_reaction", "value": "40"}
    ]
  }
]

那可以用下面的查询语句，记得把your_table_name换成你实际的表名：

SELECT SUM(CAST(jt.value AS UNSIGNED)) AS total_post_reactions
FROM your_table_name,
     JSON_TABLE(
         ad_insights,
         '$[0].actions[*]' COLUMNS(
             action_type VARCHAR(50) PATH '$.action_type',
             value VARCHAR(20) PATH '$.value'
         )
     ) AS jt
WHERE jt.action_type = 'post_reaction';

代码解释： #

• JSON_TABLE把ad_insights里第一个元素的actions数组拆成一行行的记录，每行包含action_type和value两个字段
• WHERE子句筛选出action_type等于post_reaction的记录
• CAST(jt.value AS UNSIGNED)把JSON里的字符串类型value转成整数，再用SUM求和，就能得到你要的总和啦

如果ad_insights数组里有多个元素都包含actions，需要统计所有元素里的post_reaction，只需要把JSON_TABLE里的路径改成'$[*].actions[*]'就行。

方案二：MySQL 5.7及以下（无JSON_TABLE支持） #

如果你的MySQL版本低于8.0，没办法用JSON_TABLE，可以用下标循环的方式处理：

SELECT SUM(
    CAST(
        JSON_UNQUOTE(
            JSON_EXTRACT(ad_insights, CONCAT('$[0].actions[', idx, '].value'))
        ) AS UNSIGNED
    )
) AS total_post_reactions
FROM your_table_name,
     (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS indexes
WHERE JSON_UNQUOTE(JSON_EXTRACT(ad_insights, CONCAT('$[0].actions[', idx, '].action_type'))) = 'post_reaction'
AND idx < JSON_LENGTH(JSON_EXTRACT(ad_insights, '$[0].actions'));

代码解释： #

• 先创建了一个临时的indexes表，包含0到4的下标（如果你的actions数组元素更多，可以继续加UNION ALL SELECT 5这类语句）
• 通过CONCAT拼接下标，逐个取出actions数组里每个元素的action_type和value
• 过滤出符合post_reaction的项，同时判断下标小于数组长度避免越界，最后求和

记得根据你的实际JSON结构调整路径里的$[0]部分哦～

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