在MariaDB 10.5.x中提取JSON数组最小值（无法使用JSON_TABLE）

假设你的表名为my-table，存储JSON的字段名为json_data（实际字段名不同请自行替换），可以通过递归CTE生成数组索引结合JSON函数实现需求，具体SQL如下：

WITH RECURSIVE index_seq AS (
    -- 初始化：生成第一个索引0
    SELECT 0 AS idx
    UNION ALL
    -- 递归生成后续索引，直到覆盖表中最长数组的长度
    SELECT idx + 1 
    FROM index_seq
    WHERE idx + 1 < (SELECT MAX(JSON_LENGTH(json_data->'$.arrayOfDates')) FROM `my-table`)
)
SELECT 
    t.id, -- 替换为你的表主键/唯一标识字段
    MIN(STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(t.json_data, CONCAT('$.arrayOfDates[', s.idx, ']'))), '%Y-%m-%d')) AS min_date
FROM `my-table` t
JOIN index_seq s 
    ON s.idx < JSON_LENGTH(t.json_data->'$.arrayOfDates')
WHERE 
    -- 过滤无法转换为有效日期的无效元素
    STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(t.json_data, CONCAT('$.arrayOfDates[', s.idx, ']'))), '%Y-%m-%d') IS NOT NULL
GROUP BY t.id;

关键逻辑说明： #

• 递归CTE index_seq：生成从0开始的连续整数序列，用来遍历JSON数组的每个索引位置，确保覆盖所有可能的数组元素。
• 提取并处理数组元素：用JSON_EXTRACT拼接索引路径获取单个元素，JSON_UNQUOTE去除字符串引号，再通过STR_TO_DATE转换为标准日期类型。
• 过滤无效日期：排除转换失败的无效值（比如你示例中的'2021-01, 12'会返回NULL，被WHERE条件过滤）。
• 分组取最小值：按每条记录的唯一标识分组，用MIN()得到该记录日期数组中的最小有效日期。

额外注意： #

1. 若arrayOfDates可能为空数组，JSON_LENGTH返回0时该记录不会出现在结果中，需保留的话可改用LEFT JOIN并处理NULL值。
2. 若JSON中的日期格式不同，要调整STR_TO_DATE的格式参数（比如%Y/%m/%d）。
3. 大表场景下，递归CTE性能可能受限，可预先创建一个数字辅助表（存储0到足够大的整数）替代递归生成索引，提升效率。

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