MySQL5.7.x JSON数据查询问题：筛选outcome_id=418的活动

Hey there, I've got you covered on this MySQL JSON query issue. Let's break down how to find the student with student_id=10 whose activities JSON field includes an activity that has an outcome_id of 418.

MySQL 5.7 has a set of JSON functions that can handle this nested array scenario. Here are two reliable approaches:

方法1：使用JSON_CONTAINS #

This function checks if a specific JSON value exists within a given JSON document or path. For your nested structure, we'll use a recursive path to target all outcomes arrays:

SELECT *
FROM your_table_name
WHERE student_id = 10
  AND JSON_CONTAINS(activities, '{"outcome_id": "418"}', '$**.outcomes');

解释： #

• $**.outcomes是递归路径表达式，告诉MySQL在activities JSON结构的所有位置查找名为outcomes的数组。
• JSON_CONTAINS会验证这些outcomes数组中是否存在匹配{"outcome_id": "418"}的元素。

方法2：使用JSON_SEARCH #

如果你更倾向于直接检查值的存在性，JSON_SEARCH会返回匹配值的路径（如果没有匹配则返回NULL）：

SELECT *
FROM your_table_name
WHERE student_id = 10
  AND JSON_SEARCH(activities, 'one', '418', NULL, '$**.outcome_id') IS NOT NULL;

解释： #

• 'one'表示找到第一个匹配项后就停止搜索（如果需要所有匹配项可以用'all'，但对于存在性检查，'one'效率更高）。
• $**.outcome_id会定位嵌套结构中所有的outcome_id字段。如果存在值为'418'的匹配项，函数会返回非空路径，我们通过判断非空来筛选记录。

记得把your_table_name替换成你实际的表名。这两种方法都能很好地处理你在MySQL 5.7.x中的嵌套JSON结构查询需求。

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