Node.js对接SQL Server使用FOR JSON PATH返回结果不符合预期如何解决

问题根因 #

SQL Server执行带FOR JSON后缀的查询时，不会直接返回结构化的JSON对象，只会返回单行单列的结果：列名为系统自动生成的固定值JSON_F52E2B61-18A1-11d1-B105-00805F49916B，值为完整的JSON序列化字符串。你之前直接把recordset赋值给data，就会出现不符合预期的结构，之前尝试的序列化/反序列化操作不生效，也是因为没有先提取到实际的JSON字符串内容。

方案1：Node.js侧调整（改动最小） #

直接提取结果里的JSON字符串后反序列化即可，修改赋值逻辑：

if (recordset.rowsAffected[0] > 0) {
    jsonResponse.success = 1
    // 提取第一行第一列的JSON字符串，反序列化后赋值给data
    const jsonStr = recordset.recordset[0][Object.keys(recordset.recordset[0])[0]]
    jsonResponse.data = JSON.parse(jsonStr)
}
else {
    // 注意原代码这里变量名首字母大写，和前面定义的jsonResponse不一致，需要修正避免报错
    jsonResponse.success = 0;
    jsonResponse.message = "ERR 1:Invalid credentials";
}

方案2：SQL侧调整（结构更可控） #

去掉查询最外层的FOR JSON PATH后缀，只保留子查询的FOR JSON PATH，这样查询会返回正常的二维表结构，每行的products字段为子查询生成的JSON字符串，你只需要单独对products字段做反序列化即可：

修改后的SQL语句 #

SELECT codeid, code, validFrom, validTo,
(SELECT dbo.PLprospectAgentCodesComp.productIdentifier, dbo.masterGroupsProducts.productName, dbo.PLprospectAgentCodesComp.compensation
FROM dbo.PLprospectAgentCodesComp INNER JOIN
dbo.masterGroupsProducts ON dbo.PLprospectAgentCodesComp.productIdentifier = dbo.masterGroupsProducts.productIdentifier
WHERE (dbo.PLprospectAgentCodesComp.codeid = dbo.PLprospectAgentCodes.codeid) FOR JSON PATH ) as products
FROM dbo.PLprospectAgentCodes
WHERE (plid = @plid)

对应的Node.js处理逻辑 #

// 建议使用参数化查询避免SQL注入风险，不要直接拼接变量到SQL语句
let recordset = await conn.query({
  text: sqlString,
  parameters: [{ name: 'plid', value: userData.plid }]
})
if (recordset.rowsAffected[0] > 0) {
    jsonResponse.success = 1
    // 遍历每行，把products字段从字符串转为JSON对象
    jsonResponse.data = recordset.recordset.map(item => {
      item.products = JSON.parse(item.products)
      return item
    })
}

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