Express.js合并SQLite左连接冗余行生成嵌套JSON方法

你遇到的是JOIN查询返回平铺结果的经典问题，不需要换ORM或者改表结构，以下两种方案都可以稳定输出你要的结构，根据项目场景选即可。

方案1：应用层结果重组（通用无兼容问题，推荐优先使用） #

不需要修改你现有的SQL查询语句，只需要在拿到数据库返回的平铺结果后，做一次遍历聚合即可，兼容所有关系型数据库，边界逻辑可控。
替换你原有db.all回调内的逻辑：

db.all(sql, (err, rows) => {
  if(err){
    // 记得加return，避免错误触发后继续执行后续逻辑
    return res.status(400).json({"error": err.message});
  }
  const productMap = new Map();
  for (const row of rows) {
    // 首次遍历到某个商品时，初始化基础结构
    if (!productMap.has(row.id)) {
      productMap.set(row.id, {
        id: row.id,
        name: row.name,
        flaws: []
      });
    }
    // 处理无瑕疵的边界场景：LEFT JOIN关联不到Flaw时desc为null，跳过空值
    if (row.desc != null) {
      productMap.get(row.id).flaws.push({ desc: row.desc });
    }
  }
  // Map转数组即为前端要求的嵌套结构
  res.status(200).json(Array.from(productMap.values()));
});

方案优势：

• 无SQL方言兼容问题，不管你后续从SQLite换到MySQL/PostgreSQL都不需要改逻辑
• 边界场景覆盖完整：无关联瑕疵的商品会返回空的flaws数组，不会出现null或者脏数据
• 性能开销极低，千级以内数据量的遍历耗时可以忽略

方案2：SQL层直接聚合（适合大数据量场景） #

如果单次查询的商品量过万，可以把聚合逻辑下推到数据库层执行，减少应用层的内存占用，注意不同数据库的JSON聚合函数有差异，以Node生态最常用的SQLite为例，修改原SQL语句如下：

SELECT 
  Product.id,
  Product.name,
  -- 聚合关联瑕疵为JSON数组，无关联数据时返回空数组
  COALESCE(
    JSON_GROUP_ARRAY(JSON_OBJECT('desc', Flaw.desc)),
    JSON_ARRAY()
  ) AS flaws
FROM Product
LEFT JOIN Flaw ON Flaw.productId = Product.id
GROUP BY Product.id, Product.name

数据库函数适配提示：如果使用MySQL，将JSON_GROUP_ARRAY替换为JSON_ARRAYAGG即可；如果使用PostgreSQL，替换为json_agg(json_build_object('desc', Flaw.desc))。

注意：SQL类数据库返回的聚合JSON字段默认是字符串格式，拿到结果后需要做一次解析：

db.all(sql, (err, rows) => {
  if(err){
    return res.status(400).json({"error": err.message});
  }
  // 解析flaws字段为JS数组
  const result = rows.map(row => ({
    ...row,
    flaws: JSON.parse(row.flaws)
  }));
  res.status(200).json(result);
});

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