如何在数据库层面查询全表按device_id分组返回键值对象

API 探路者

2026-8-27

解答

完全可以在数据库层面直接实现你要的返回结构，无需额外在应用层做组装，不同数据库的实现语法有差异，以下是主流关系型数据库的可直接运行的实现方式：

内层查询先按device_id分组，把同设备下的记录聚合成包含id、text字段的对象数组
外层查询把分组后的device_id作为键，对应的对象数组作为值，聚合成完整的键值对JSON对象

MySQL 5.7及以上版本实现

使用MySQL内置的JSON聚合函数即可，注意MySQL的JSON对象键默认是字符串类型，查询语句：

SELECT JSON_OBJECTAGG(device_id, msg_list) AS device_msg_map
FROM (
    SELECT
        device_id,
        JSON_ARRAYAGG(JSON_OBJECT('id', id, 'text', `text`)) AS msg_list
    FROM 替换为你的实际表名
    GROUP BY device_id
) AS grouped_msg;

查询返回的device_msg_map字段就是你需要的完整结构，直接序列化返回给前端即可使用。

PostgreSQL 实现

PostgreSQL支持数字类型作为JSON键，和你期望的格式完全一致，查询语句：

SELECT jsonb_object_agg(device_id, msg_list) AS device_msg_map
FROM (
    SELECT
        device_id,
        jsonb_agg(jsonb_build_object('id', id, 'text', text)) AS msg_list
    FROM 替换为你的实际表名
    GROUP BY device_id
) AS grouped_msg;

如果不需要二进制JSONB格式，把函数前缀的jsonb_替换为json_即可返回标准JSON格式结果。