MySQL5.7.x JSON数据更新与查询求助：查找outcome_id=418的活动

针对你的需求，我们可以利用MySQL 5.7提供的JSON函数来定位并提取符合条件的数据。因为MySQL 5.7还没有引入JSON_TABLE（这个是8.0+的特性），所以我们得用JSON_CONTAINS、JSON_SEARCH和JSON_EXTRACT这些函数来实现。

第一步：先确认是否存在目标记录 #

首先可以用JSON_CONTAINS来快速判断student_id=3531的记录中，是否包含outcome_id为"418"的项：

SELECT *
FROM 2017_assessment_data
WHERE student_id = 3531
  AND JSON_CONTAINS(activities->'$.activities[*].outcomes[*].outcome_id', '"418"');

这里的activities->'$.activities[*].outcomes[*].outcome_id'会遍历所有活动下的所有outcome的outcome_id，JSON_CONTAINS检查其中是否有等于"418"的值（注意JSON字符串要带双引号，所以用"418"）。

第二步：提取对应的活动数据 #

如果要拿到具体的活动项（也就是包含outcome_id=418的那个activities数组元素），可以结合JSON_SEARCH和JSON_EXTRACT来实现：

SELECT 
  JSON_EXTRACT(
    activities,
    SUBSTRING_INDEX(JSON_UNQUOTE(JSON_SEARCH(activities, 'one', '418', NULL, '$.activities[*].outcomes[*].outcome_id')), '.', 3)
  ) AS matching_activity
FROM 2017_assessment_data
WHERE student_id = 3531
  AND JSON_CONTAINS(activities->'$.activities[*].outcomes[*].outcome_id', '"418"');

• JSON_SEARCH会返回第一个匹配到的outcome_id的路径，比如"$.activities[0].outcomes[1].outcome_id"
• JSON_UNQUOTE去掉路径字符串的双引号，得到$.activities[0].outcomes[1].outcome_id
• SUBSTRING_INDEX(..., '.', 3)截取路径的前3部分，得到$.activities[0]，这就是对应的活动项的路径
• JSON_EXTRACT根据这个路径提取出整个活动对象

如果有多个匹配的活动项 #

如果你的JSON数组里有多个活动包含outcome_id=418，JSON_SEARCH的第二个参数改成'all'会返回所有匹配的路径数组，但MySQL 5.7没有直接的方法把这些路径转成多行结果。这种情况下，你可能需要写一个存储过程来遍历路径数组，或者升级到MySQL 8.0使用JSON_TABLE来更方便地处理数组转表的操作。

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