PostgreSQL配合Node.js如何单查询返回分组嵌套JSON传感器数据

完全可以实现，PostgreSQL原生提供的JSON构造、聚合函数支持直接生成你需要的嵌套结构，不需要在Node.js业务层做二次数据重组。

可直接使用的SQL语句 #

假设你的传感器读数表名为sensor_readings，执行以下SQL即可得到完全匹配要求的JSON结果：

SELECT
  json_agg(
    json_build_object(
      'sensorID', sensorID,
      'data', reading_list
    )
    ORDER BY sensorID
  ) AS sensor_data
FROM (
  SELECT
    sensorID,
    json_agg(
      json_build_object(
        'timestamp', timestamp,
        'temperature', temperature
      )
      ORDER BY timestamp
    ) AS reading_list
  FROM sensor_readings
  GROUP BY sensorID
) grouped_data;

语句逻辑说明 #

• 内层子查询负责按sensorID分组，通过json_agg将同个传感器下的所有温度、时间读数聚合成JSON数组，对应输出结构里的data字段；语句里的ORDER BY timestamp可以保证每个传感器下的读数按采集时间正序排列，不需要的话可以直接删除提升查询性能。
• 外层查询将分组得到的sensorID和对应读数数组组装成单个传感器对象，再通过json_agg把所有传感器对象聚合成顶层数组，和你给出的目标结构完全一致。

如果你使用官方pg驱动连接数据库，查询返回结果中rows[0].sensor_data就是可以直接使用的原生JavaScript数组，驱动会自动完成PostgreSQL JSON类型到JS对象的转换，不需要额外调用JSON.parse处理。

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