在MySQL及MariaDB中实现PostgreSQL式JSON数组列行排序

实现思路 #

PostgreSQL的原生数组排序逻辑为：按元素位置从左到右逐位比较数值大小，短数组与长数组前缀完全匹配时，短数组排在前面。我们可以通过拆解JSON数组元素、生成等长排序键的方式完全复刻该逻辑，不需要提前预知数组长度和元素取值范围。

通用实现方案（兼容MySQL 8.0+/MariaDB 10.2+） #

基于递归CTE自动适配任意长度的数组，排序结果和PostgreSQL完全一致：

WITH RECURSIVE array_data AS (
    -- 此处替换为你的实际业务数据查询
    SELECT json_array(2, 4) AS `array` UNION ALL
    SELECT json_array(10) AS `array` UNION ALL
    SELECT json_array(2, 3, 4) AS `array` UNION ALL
    SELECT json_array(10, 11) AS `array`
),
positions AS (
    -- 递归生成数组位置序列，自动适配所有数组的最大长度
    SELECT 1 AS pos UNION ALL
    SELECT pos + 1 FROM positions WHERE pos < (SELECT MAX(json_length(`array`)) FROM array_data)
)
SELECT ad.`array`
FROM array_data ad
LEFT JOIN positions p ON p.pos <= json_length(ad.`array`)
GROUP BY ad.`array`
ORDER BY GROUP_CONCAT(
    -- 每个数字补零到10位，保证字符串比较和数值比较结果一致，可根据实际数值范围调整长度
    LPAD(json_unquote(json_extract(ad.`array`, CONCAT('$[', p.pos-1, ']'))), 10, '0') 
    ORDER BY p.pos ASC SEPARATOR '.'
) ASC;

执行后得到的排序结果为：[2, 3, 4]、[2, 4]、[10]、[10, 11]，和PostgreSQL效果完全一致。

低版本兼容方案（适配无CTE的MySQL 5.x/旧版MariaDB） #

如果数据库版本不支持递归CTE，可以使用正则替换补位的简化方案，正数场景下效果一致：

SELECT `array`
FROM (
    -- 此处替换为你的实际业务数据查询
    SELECT json_array(2, 4) AS `array` UNION ALL
    SELECT json_array(10) AS `array` UNION ALL
    SELECT json_array(2, 3, 4) AS `array` UNION ALL
    SELECT json_array(10, 11) AS `array`
) t
ORDER BY REGEXP_REPLACE(
    REGEXP_REPLACE(`array`, '\\[(.*)\\]', '$1'),
    '([0-9]+)', 
    LPAD('\\1', 10, '0')
) ASC;

方案优势 #

• 无需提前预知数组的最大长度，自动适配所有待排序数组的长度
• 无需提前预知元素的取值范围，默认10位补位可覆盖所有INT类型整数，大数值场景仅需调整补位长度即可
• 完全匹配PostgreSQL的排序逻辑，无字符串排序、长短数组排序错位的问题

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