MySQL JSON列嵌套数组筛选：提取第9元素为指定值的子数组

假设你的表名为your_table，存储JSON数据的列名为json_column，且仅存在id=1的一行数据，可以使用以下SQL语句实现需求：

SELECT JSON_ARRAYAGG(j.sub_array) AS filtered_array
FROM your_table t,
     JSON_TABLE(t.json_column,
                '$[*]' COLUMNS (sub_array JSON PATH '$')) j
WHERE t.id = 1
  AND JSON_UNQUOTE(JSON_EXTRACT(j.sub_array, '$[8]')) = '1';

关键说明： #

• JSON_TABLE用于将外层JSON数组拆分为多行，每行对应一个子数组（仅MySQL 8.0及以上版本支持该函数）
• $[8]对应子数组的第9个元素（MySQL JSON数组索引从0开始计数）
• 若目标元素是数值类型，可省略JSON_UNQUOTE，直接用JSON_EXTRACT(j.sub_array, '$[8]') = 1进行判断
• JSON_ARRAYAGG将筛选后的子数组重新聚合成一个新的JSON数组

内容的提问来源于stack exchange，提问作者Özgür AKSU