MySQL JSON字段中如何用通配符匹配Session名称查询r_type='main'的会话

我来帮你搞定这个动态Session编号的查询需求！因为Session的键名（比如Session0、Session1）不固定，直接用JSON_EXTRACT指定固定路径肯定行不通，不过MySQL 8.0及以上版本提供的JSON_TABLE函数正好能处理这种动态键的场景，咱们一步步来实现：

核心思路 #

1. 提取所有Session键：先用JSON_KEYS函数把JSON字段里的所有顶级Session键（比如Session0、Session1）转换成行数据。
2. 展开每个Session的数组：对每个Session键，用JSON_TABLE把对应的数组对象拆成单独的行，提取出r_type字段。
3. 筛选并关联原表：筛选出r_type = 'main'的记录，再关联回原表，就能得到所有符合条件的Session了。

具体SQL语句 #

假设你的表名叫your_table，JSON字段名叫session_field，可以用下面的查询语句：

SELECT DISTINCT 
  t.id, -- 原表主键，根据你的实际字段调整
  keys.session_key,
  JSON_EXTRACT(t.session_field, CONCAT('$.', keys.session_key)) AS session_details
FROM your_table t
-- 第一步：提取所有Session键并转成行
JOIN JSON_TABLE(
  JSON_KEYS(t.session_field),
  '$[*]' COLUMNS(session_key VARCHAR(50) PATH '$')
) keys
-- 第二步：展开每个Session下的数组，提取r_type
JOIN JSON_TABLE(
  JSON_EXTRACT(t.session_field, CONCAT('$.', keys.session_key)),
  '$[*]' COLUMNS(r_type VARCHAR(50) PATH '$.r_type')
) items
-- 第三步：筛选出r_type为main的记录
WHERE items.r_type = 'main';

语句解释 #

• JSON_KEYS(t.session_field)：获取JSON字段里的所有顶级键，也就是所有Session编号。
• 第一个JSON_TABLE：把这些Session键转换成一行一行的记录，方便后续循环处理。
• CONCAT('$.', keys.session_key)：拼接出每个Session的JSON路径（比如$.Session0），用来提取对应的数组。
• 第二个JSON_TABLE：把每个Session的数组拆成单独的行，提取每一项的r_type。
• DISTINCT：避免同一个Session因为有多个r_type='main'的条目而重复出现。

针对MySQL 5.7的兼容方案 #

如果你的MySQL版本是5.7（不支持JSON_TABLE），那处理起来会麻烦一些，需要结合JSON_LENGTH和自定义变量来模拟循环，但这种方法性能较差，更推荐升级到8.0版本。如果必须用5.7，可以参考这个思路：

• 先获取JSON字段里Session的数量，用JSON_LENGTH(JSON_KEYS(session_field))。
• 用变量生成从0到数量-1的序列，逐个提取每个Session键，再检查对应的数组里是否包含r_type='main'。

测试结果 #

用你提供的JSON数据测试，这个查询会返回Session0和Session2的相关信息，正好符合你的需求。

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