Node.js中使用mssql包能否直接通过SQL生成指定结构JSON

结合你提到的表结构，Table1为固定三层字段：分别存储一级分类（如animal/plant/human）、二级分类（如fish/insect/wild）、最底层具体条目（如shark/fly）。由于表内存在全字段重复记录，所有聚合操作前必须先做去重处理，避免最终JSON数组里出现重复值。

可直接使用的SQL查询语句 #

以下写法基于SQL Server 2017及以上版本（目前绝大多数线上环境均满足要求），数据库会直接输出和你示例结构完全一致的JSON字符串，不需要把原始记录全部捞到Node层再做拼接聚合。注意把语句里的cat1/cat2/item替换成你表中实际的字段名即可：

SELECT
  t1.cat1,
  JSON_QUERY(
    (
      SELECT
        t2.cat2,
        JSON_QUERY(
          CONCAT(
            '[',
            STRING_AGG(
              DISTINCT QUOTENAME(t2.item, '"'),
              ','
            ),
            ']'
          )
        )
      FROM Table1 t2
      WHERE t2.cat1 = t1.cat1
      GROUP BY t2.cat2
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )
  ) AS value
FROM (
  SELECT DISTINCT cat1 FROM Table1
) t1
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

路由代码需要小幅调整 #

这里有个容易踩的坑：SQL Server执行带FOR JSON的查询时，返回的结果集是单行单列的，列名是系统自动生成的GUID格式名称，如果直接返回查询结果，会变成外层数组包裹字符串的无效结构，需要做简单处理：

router.get("/", async (req, res) => {
  try {
    await sql.connect(`mssql://${config.get('SQL_USER')}:${config.get('SQL_PASSWORD')}@${config.get('HOST')}/${config.get('DATABASE')}`);
    // 替换为上面写好的完整SQL
    const queryResult = await sql.query`这里粘贴你写好的SQL语句`;
    // 从结果中取出SQL生成的JSON字符串，转换为对象后返回
    const resultRow = queryResult.recordset[0];
    const finalJsonStr = resultRow[Object.keys(resultRow)[0]];
    res.json(JSON.parse(finalJsonStr));
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

注意事项 #

• 如果你的SQL Server是2016版本（无内置STRING_AGG聚合函数），可以把SQL里拼接item数组的部分替换为XML路径拼接的写法，效果完全一致，不需要额外升级数据库版本。
• 语句中的DISTINCT关键字必须保留，否则因为表内存在重复记录，最终生成的数组里会出现重复的条目值。
• 写法中的JSON_QUERY是必须保留的，如果去掉这层封装，SQL会把拼好的JSON数组当成普通字符串转义，最终结构里的数组会变成带转义的文本字符串，而不是原生的JSON数组结构。
• 语句中用到的QUOTENAME会自动处理条目值里的特殊字符（比如双引号），避免生成的JSON出现语法错误，不需要手动做转义处理。

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