如何在Express.js中使用express4-tedious返回路由前修改JSON结果

Hey there! The key issue here is that into(res) in express4-tedious automatically sends the raw query result to the client—so you can't modify the data first. Instead, you need to handle the result in the done() callback, transform it just like you did with pg-promise, then manually send the response yourself.

Let's break down the correct approach with two options, depending on whether you want to use SQL Server's FOR JSON PATH or not:

Option 1: Directly Process Rows (Most Similar to Your Original pg-promise Code) #

This is the simplest approach since it mirrors your original workflow exactly. We'll fetch the raw rows from the query, then apply your existing transformation logic:

app.get('/heatmapData', function(req, res) {
  req.sql(`
    SELECT id, metricname, metricval as value, backgroundcolor as fill, suggestedtextcolor as color, heatmapname
    FROM heatmapdata a
    INNER JOIN heatmapcolors b 
      ON a.heatmapset = heatmapname and a.heatmapnumber = b."Order"
  `)
  .done((rows) => {
    // Reuse your original transformation logic here
    let bob = {};
    rows.map(item => {
      if (bob[item.metricname] === undefined) {
        bob[item.metricname] = {};
      }
      bob[item.metricname][item.id] = {
        fill: item.fill,
        color: item.color,
        value: item.value // Fixed case here—your SQL uses `metricval as value` not `Value`
      };
      bob[item.metricname].heatmapname = item.heatmapname;
    });
    // Send the transformed data manually
    res.status(200).json(bob);
  })
  .fail((err) => {
    // Don't forget error handling!
    res.status(500).json({ error: 'Failed to fetch heatmap data', details: err.message });
  });
});

Why this works: #

• The done() callback receives rows, an array of objects representing each row from your query—just like the data parameter in your pg-promise .then() handler.
• We skip into(res) entirely, so we have full control over the data before sending it to the client.
• Added a fail() handler to catch any database errors and return a meaningful error response (something your original code was missing, but good practice!).

Option 2: Using SQL Server's FOR JSON PATH #

If you still want to use FOR JSON PATH to generate JSON directly in SQL, you'll need to parse the JSON string returned by SQL Server before transforming it:

app.get('/heatmapData', function(req, res) {
  req.sql(`
    SELECT id, metricname, metricval as value, backgroundcolor as fill, suggestedtextcolor as color, heatmapname
    FROM heatmapdata a
    INNER JOIN heatmapcolors b 
      ON a.heatmapset = heatmapname and a.heatmapnumber = b."Order"
    FOR JSON PATH
  `)
  .done((result) => {
    // SQL Server returns the JSON as a single row with a generated key
    const jsonKey = Object.keys(result[0])[0];
    const rawData = JSON.parse(result[0][jsonKey]);
    
    // Apply your transformation logic to rawData
    let bob = {};
    rawData.map(item => {
      if (bob[item.metricname] === undefined) {
        bob[item.metricname] = {};
      }
      bob[item.metricname][item.id] = {
        fill: item.fill,
        color: item.color,
        value: item.value
      };
      bob[item.metricname].heatmapname = item.heatmapname;
    });
    
    res.status(200).json(bob);
  })
  .fail((err) => {
    res.status(500).json({ error: 'Failed to fetch heatmap data', details: err.message });
  });
});

Note: #

This option adds an extra step of parsing the JSON string, which isn't necessary here since your transformation logic works perfectly with raw rows. Stick with Option 1 unless you have a specific reason to use FOR JSON PATH.

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