如何在MySQL查询中对JSON返回值的data数组按指定列排序？

表结构 #

现有currency表结构及数据如下：

原查询 #

当前使用的MySQL查询如下：

SELECT json_arrayagg(obj)
FROM (
    SELECT json_object(
                      'currency', 'rupee',
                      'data', json_arrayagg(json_array(datetime, rupee))
                      ) AS obj
    FROM currency
    UNION ALL
    SELECT json_object(
                      'currency', 'pound',
                      'data', json_arrayagg(json_array(datetime, pound))
                      )
    FROM currency
   ) x;

需求 #

该查询返回的JSON结构符合预期，但需要让每个对象的data数组按对应列（Rupee或Pound）升序排序。注意Rupee和Pound列类型为varchar(10)，值带百分号。预期结果如下：

[{"currency": "Rupee", "data": [["2019-02-17", "30%"], ["2019-02-18", "67%"], ["2019-02-16", "80%"]}],
 {"currency": "pound", "data": [["2019-02-17", "60%"], ["2019-02-16", "70%"], ["2019-02-18", "89%"]]}]

解决方案 #

要实现这个需求，需要在json_arrayagg聚合前，先对每条记录按处理后的数值排序。因为列值是带%的字符串，需要先去掉百分号转成数值再排序。修改后的查询如下：

SELECT json_arrayagg(obj)
FROM (
    -- 处理Rupee的排序
    SELECT json_object(
                      'currency', 'rupee',
                      'data', json_arrayagg(json_array(datetime, rupee) ORDER BY CAST(REPLACE(Rupee, '%', '') AS UNSIGNED) ASC)
                      ) AS obj
    FROM currency
    UNION ALL
    -- 处理Pound的排序
    SELECT json_object(
                      'currency', 'pound',
                      'data', json_arrayagg(json_array(datetime, pound) ORDER BY CAST(REPLACE(Pound, '%', '') AS UNSIGNED) ASC)
                      )
    FROM currency
   ) x;

关键说明 #

1. 排序时机：直接在json_arrayagg内部添加ORDER BY子句，确保聚合时按指定顺序生成数组。
2. 字符串转数值：用REPLACE(Rupee, '%', '')去掉百分号，再通过CAST(... AS UNSIGNED)转成无符号整数，避免字符串排序导致的错误（比如"80%"会被误判为比"30%"小的问题）。
3. 排序方向：如果需要降序，把ASC改成DESC即可。

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