如何从MariaDB生成指定结构的嵌套JSON？（Node.js后端）

针对你的需求，使用MariaDB 10.8的JSON聚合函数可以实现单查询生成指定嵌套结构，同时通过显式排序保证readings数组顺序稳定。

最终SQL查询 #

SELECT
  station_id,
  datetime,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'dimension', ud.dimension,
      'value', r.value,
      'representation', u.representation,
      'unit', u.unit,
      'unit_id', u.unit_id
    ) ORDER BY ud.dimension ASC -- 指定排序字段，保证数组顺序稳定
  ) AS readings
FROM Readings r
JOIN Units_dimension ud ON r.dimension_id = ud.id -- 根据实际表关联字段调整
JOIN Units u ON ud.unit_id = u.unit_id -- 根据实际表关联字段调整
GROUP BY station_id, datetime
ORDER BY datetime DESC; -- 控制结果集整体顺序，可选

关键说明 #

1. JSON_OBJECT组装单个读数对象：将每个维度的字段（dimension、value等）打包成符合要求的JSON对象，对应嵌套结构里的readings数组元素。
2. JSON_ARRAYAGG聚合生成数组：通过分组station_id和datetime，将同一站点同一时间的所有读数对象聚合为readings数组。
3. 显式排序解决顺序不稳定问题：在JSON_ARRAYAGG内部添加ORDER BY ud.dimension ASC（可替换为unit_id或其他业务需要的字段），默认聚合函数的结果顺序无保证，必须显式指定排序规则才能让数组顺序固定。
4. 表关联适配：请根据你的实际表结构调整JOIN条件中的关联字段（示例中假设Readings通过dimension_id关联Units_dimension，Units_dimension通过unit_id关联Units）。

结果验证 #

执行上述查询后，返回的JSON结构将完全匹配你期望的嵌套格式，且readings数组内的元素会始终按照指定字段排序，不会出现顺序波动。

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