MySQL中获取JSON数组元素索引的技术咨询

我明白你遇到的困扰——在MySQL里处理数值型JSON数组时，想获取指定元素的索引却返回null，这确实容易踩坑。我来给你详细讲可行的解决方案，以及为什么之前的查询会失败。

为什么你的查询返回null？ #

大概率是你用了JSON_SEARCH函数但没注意类型匹配问题。JSON_SEARCH默认是按字符串类型搜索的，而你的数组里的元素是数值型（比如9是数字，不是字符串"9"），所以它找不到匹配项，自然返回null。比如执行这个查询就会得到null：

SELECT JSON_SEARCH('[16, 9, 11, 22, 23, 12]', 'one', 9);

推荐解决方案：用JSON_TABLE展开数组 #

MySQL 8.0及以上版本支持JSON_TABLE函数，它可以把JSON数组转换成带序号的关系表，这样就能轻松筛选目标值并获取索引。

示例代码 #

SELECT idx - 1 AS element_index
FROM JSON_TABLE(
    '[16, 9, 11, 22, 23, 12]',
    '$[*]' COLUMNS(
        idx FOR ORDINALITY,  -- 生成从1开始的序号
        value INT PATH '$'   -- 提取数组中的数值元素
    )
) AS jt
WHERE value = 9;

执行后会返回1，正好是你要的9的索引（0-based）。

补充说明 #

• FOR ORDINALITY会给每个数组元素分配一个从1开始的序号，减去1就得到了我们常用的0-based索引。
• 如果数组中有重复的目标值，这个查询会返回所有匹配的索引（比如数组里有两个9，会返回对应的两个索引值）。

备选方案：调整JSON_SEARCH的用法（仅适用于特殊场景） #

如果你一定要用JSON_SEARCH，可以把搜索值转换成字符串，同时用JSON_UNQUOTE去掉路径的引号，再提取索引部分：

SELECT 
  CAST(SUBSTRING(JSON_UNQUOTE(JSON_SEARCH('[16, 9, 11, 22, 23, 12]', 'one', '9')), 4) AS UNSIGNED) - 1 AS element_index;

但这种方法有局限性：如果数组里的元素既有数值又有字符串，或者目标值本身是字符串，容易出错，所以更推荐第一种方法。

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