SQLite中json_group_array排序失效问题求助

在SQLite中，json_group_array不会自动继承外层查询的排序规则，必须确保聚合前的数据已按预期排序，同时内层子查询的排序逻辑也需要正确传递给聚合函数。以下是两种可行的修正方案：

方案一：提前排序再聚合（兼容所有SQLite版本） #

SELECT json_group_array( 
        json_object(
            'trip_id', t.id, 
            'trip_desc', t.desc,
            'trip_files', (
                SELECT json_group_array(file_obj)
                FROM (
                    SELECT json_object('file_id', f.id, 'file_desc', f.desc) AS file_obj
                    FROM files f
                    WHERE f.trip = t.id 
                    ORDER BY f.id ASC
                ) sorted_files
            )
        )
    )
FROM (
    SELECT * 
    FROM trips t
    WHERE t.year IN (SELECT id FROM years WHERE id=1)
    ORDER BY t.id ASC
) sorted_trips
;

关键调整点 #

• 外层Trip排序：通过子查询sorted_trips先对trips数据按id升序排序，再传入json_group_array聚合，确保外层JSON数组按trip_id顺序生成。
• 内层Files排序：新增子查询sorted_files，先对files按id升序排序并生成单个文件的JSON对象，再将排序后的结果传入json_group_array，避免聚合函数忽略原排序规则。

方案二：使用json_group_array内置排序（SQLite 3.33.0+） #

如果你的SQLite版本在3.33.0及以上，可以直接利用json_group_array的内置排序参数，写法更简洁：

SELECT json_group_array( 
        json_object(
            'trip_id', t.id, 
            'trip_desc', t.desc,
            'trip_files', (
                SELECT json_group_array(
                    json_object('file_id', f.id, 'file_desc', f.desc),
                    ORDER BY f.id ASC
                )
                FROM files f
                WHERE f.trip = t.id
            )
        ),
        ORDER BY t.id ASC
    )
FROM trips t
WHERE t.year IN (SELECT id FROM years WHERE id=1)
;

这种写法直接在json_group_array函数内指定排序规则，无需额外嵌套子查询，逻辑更直观。

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