如何在MySQL中编写针对JSON数组对象的筛选查询语句

以下语句适用于MySQL 5.7及以上版本，语句中的?为输入值占位符，实际使用时替换为要查询的数值即可：

SELECT * 
FROM students
WHERE 
  -- 规则1：stdran长度大于0且包含指定输入值
  (JSON_LENGTH(val, '$.stdran') > 0 AND JSON_CONTAINS(val->'$.stdran', CAST(? AS JSON)))
  -- 规则2：满足任意一个兜底场景即可返回
  OR JSON_CONTAINS(val, '{}')
  OR JSON_CONTAINS_PATH(val, 'one', '$.stdran') = 0
  OR JSON_LENGTH(val, '$.stdran') = 0;

匹配逻辑说明 #

• 规则1匹配有有效stdran数组且包含目标值的记录
• 规则2覆盖三类不需要匹配数组值的场景：
  • val字段为空白JSON对象{}
  • val字段未定义stdran属性
  • val字段的stdran属性为空数组

效果验证 #

• 输入值为1时，返回id为1、3、4、5、6的记录，符合预期
• 输入值为4时，返回id为2、4、5、6的记录，符合预期

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