MySQL 5.7查询JSON数组列满足指定字段条件的SQL实现方法

MySQL 5.7 内置了完整的JSON操作函数，不需要借助外部脚本，直接用SQL即可实现你的需求，核心思路是先定位到animal为horse的对应body字段，再通过字符串长度差计算关键词出现次数。

实现SQL #

假设你的表名为your_table，存储JSON数组的列名为json_col，参考语句如下：

SELECT *
FROM your_table
WHERE 
  -- 先过滤存在animal=horse元素的行，避免无匹配时计算报错
  JSON_SEARCH(json_col, 'one', 'horse', NULL, '$[*].animal') IS NOT NULL
  AND
  -- 计算grooming关键词的出现次数
  (
    CHAR_LENGTH(
      -- 取出horse对应data.body的纯文本值
      json_col->>REPLACE(JSON_UNQUOTE(JSON_SEARCH(json_col, 'one', 'horse', NULL, '$[*].animal')), '.animal', '.data.body')
    )
    -
    CHAR_LENGTH(
      REPLACE(
        json_col->>REPLACE(JSON_UNQUOTE(JSON_SEARCH(json_col, 'one', 'horse', NULL, '$[*].animal')), '.animal', '.data.body'),
        'grooming',
        ''
      )
    )
  ) / CHAR_LENGTH('grooming') > 1;

逻辑说明 #

• JSON_SEARCH(json_col, 'one', 'horse', NULL, '$[*].animal')：匹配JSON数组中第一个animal值为horse的元素路径，返回结果格式类似"$[2].animal"
• 用REPLACE把路径中的.animal替换为.data.body，即可得到对应horse元素的body字段路径
• ->>是MySQL 5.7支持的JSON取值运算符，等价于JSON_UNQUOTE(JSON_EXTRACT())，可以直接取出不带引号的纯文本内容
• 用「原始字符串长度 - 去掉关键词后的字符串长度」除以关键词长度，即可得到关键词的出现次数，这个是MySQL统计字符串子串出现次数的通用原生方案

注意事项 #

如果你的业务场景中同一个JSON数组里可能存在多个animal为horse的元素，上述语句只会校验第一个匹配的元素。如果需要校验所有匹配元素都满足条件，可将JSON_SEARCH的第二个参数one改为all，再配合JSON遍历逻辑扩展即可。

你给出的示例数据中，horse对应的body里grooming仅出现1次，计算结果不满足>1的条件，不会被查询返回，符合你的预期。

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