MySQL中JSON_ARRAYAGG生成的JSON无法按指定字段排序求助

问题场景 #

使用JSON_ARRAYAGG和JSON_OBJECT生成JSON时，期望按t_last_updated_at字段倒序排列，但生成的JSON数组中更新时间更晚的条目（p_id=100）排在了后面，不符合预期。

原执行SQL #

select json_arrayagg(JSON_OBJECT('id',id,'p_id',p_id,'e_number',e_number,
't_last_updated_at',t_last_updated_at,
    'List',
        (select json_arrayagg(JSON_OBJECT('main_id',main_id,'quantity',quantity,'unitprice',unitprice,'created_at',created_at))
        from t_sampling s where s.id=o.id)
    )
    ) into json_output
    from t_orm o where o.id=_id    
    order by o.t_last_updated_at desc;

实际输出JSON #

json_output= [
{
    "id": 13,
    "p_id":19,
    "List": [
        {
            "main_id": 99,
            "quantity": 1,
            "unitprice": 31,
            "created_at": "2022-06-26 08:25:33.000000"
        }
    ],
    "e_number": 10,        
    "t_last_updated_at": "2022-06-26 08:25:33.000000"
},
{
    "id": 13,
    "p_id":100,
    "List": [
        {
            "main_id": 919,
            "quantity": 11,
            "unitprice": 23.5,
            "created_at": "2022-05-26 08:25:33.000000"
        }
    ],
    "e_number": 10,        
    "t_last_updated_at": "2022-07-11 08:30:33.000000"
}]

预期输出JSON #

[
{
    "id": 13,
    "p_id":100,
    "List": [
        {
            "main_id": 919,
            "quantity": 11,
            "unitprice": 23.5,
            "created_at": "2022-05-26 08:25:33.000000"
        }
    ],
    "e_number": 10,        
    "t_last_updated_at": "2022-07-11 08:30:33.000000"
},
{
    "id": 13,
    "p_id":19,
    "List": [
        {
            "main_id": 99,
            "quantity": 1,
            "unitprice": 31,
            "created_at": "2022-06-26 08:25:33.000000"
        }
    ],
    "e_number": 10,        
    "t_last_updated_at": "2022-06-26 08:25:33.000000"
}
]

错误尝试 #

• 用order by cast('$.t_last_updated_at' as DATETIME) DESC无效：'$.t_last_updated_at'是字符串字面量，无法解析为JSON字段的时间值。
• 尝试按List内quantity排序的CTE写法失败：

WITH cte AS (
SELECT DISTINCT 
       JSON_ARRAYAGG(jsontable.one_object) reordered_json
FROM test
CROSS JOIN JSON_TABLE(test.json_array,
                      '$[*]' COLUMNS (one_object JSON PATH '$' ,
                       NESTED PATH '$.List[*]' COLUMNS (q for ORDINALITY,
  quantity INT path '$.\"quantity\"'
  ))) as jsontable  
  order by q desc
  
)
SELECT JSON_PRETTY(cte.reordered_json) FROM cte

问题根源与解决方案 #

根源 #

JSON_ARRAYAGG不会直接继承外层ORDER BY的规则，外层排序仅影响最终结果集的输出顺序，而非聚合阶段的数组元素顺序。

方案1：先排序源数据再聚合 #

通过子查询先完成t_orm数据的排序，再对有序结果集执行聚合：

SELECT JSON_ARRAYAGG(obj) INTO json_output
FROM (
    SELECT JSON_OBJECT(
        'id', id,
        'p_id', p_id,
        'e_number', e_number,
        't_last_updated_at', t_last_updated_at,
        'List', (
            SELECT JSON_ARRAYAGG(JSON_OBJECT(
                'main_id', main_id,
                'quantity', quantity,
                'unitprice', unitprice,
                'created_at', created_at
            ))
            FROM t_sampling s 
            WHERE s.id = o.id
        )
    ) AS obj
    FROM t_orm o 
    WHERE o.id = _id
    ORDER BY o.t_last_updated_at DESC
) AS sorted_data;

方案2：在JSON_ARRAYAGG内部指定排序（MySQL 8.0.14+支持） #

MySQL 8.0.14及以上版本允许直接在JSON_ARRAYAGG中添加ORDER BY子句，写法更简洁：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', id,
        'p_id', p_id,
        'e_number', e_number,
        't_last_updated_at', t_last_updated_at,
        'List', (
            SELECT JSON_ARRAYAGG(JSON_OBJECT(
                'main_id', main_id,
                'quantity', quantity,
                'unitprice', unitprice,
                'created_at', created_at
            ))
            FROM t_sampling s 
            WHERE s.id = o.id
        )
    ) ORDER BY t_last_updated_at DESC
) INTO json_output
FROM t_orm o 
WHERE o.id = _id;

针对List内quantity排序的修正 #

若需按List中的quantity倒序排列主条目（假设每个主条目对应一个List元素）：

SELECT JSON_ARRAYAGG(obj) INTO json_output
FROM (
    SELECT JSON_OBJECT(
        'id', id,
        'p_id', p_id,
        'e_number', e_number,
        't_last_updated_at', t_last_updated_at,
        'List', (
            SELECT JSON_ARRAYAGG(JSON_OBJECT(
                'main_id', main_id,
                'quantity', quantity,
                'unitprice', unitprice,
                'created_at', created_at
            ))
            FROM t_sampling s 
            WHERE s.id = o.id
        )
    ) AS obj,
    (SELECT quantity FROM t_sampling s WHERE s.id = o.id LIMIT 1) AS qty
    FROM t_orm o 
    WHERE o.id = _id
    ORDER BY qty DESC
) AS sorted_data;

内容的提问来源于stack exchange，提问作者Ramaraju.d