Node with SQL Server - response with for json path query not responding as expected-CodePudding

I am working with Node and SQL Server. Everything is fine with simple two dimensional tables, but when I try to do a nested query with a for json path response, I get an unexpected result I don't know how to solve with node.js

Here is my code:

let sqlString = `
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 = ${userData.plid}) for json path`


let conn = await sql.connect(process.env.DB_CONNSTRING)
let recordset = await conn.query(sqlString)
if (recordset.rowsAffected[0] > 0) {
    jsonResponse.success = 1
    jsonResponse.data = recordset.recordset;
}
else {
    JsonResponse.success = 0;
    JsonResponse.message = "ERR 1:Invalid credentials";
}


res.json(jsonResponse)
await conn.close();

Everything was fine until I utilized for json auto at my sql string, which I need in order to get the results the way I need them.

I tried stringyfying, .toString(), JSON.parse, nothing worked, and this is the result I get:

{
    "success": 1,
    "data": [
        {
            "JSON_F52E2B61-18A1-11d1-B105-00805F49916B": "[{\"codeid\":1,\"code\":\"pablo\",\"validFrom\":\"2020-01-01\",\"validTo\":\"2022-01-01\",\"products\":[{\"productIdentifier\":\"Sigma\",\"productName\":\"Sigma Tramma\",\"compensation\":28.00},{\"productIdentifier\":\"membership\",\"productName\":\"Membership\",\"compensation\":30.00}]},{\"codeid\":2,\"code\":\"paola20\",\"validFrom\":\"2021-01-01\",\"validTo\":\"2020-01-01\",\"products\":[{\"productIdentifier\":\"Sigma\",\"productName\":\"Sigma Tramma\",\"compensation\":18.00},{\"productIdentifier\":\"membership\",\"productName\":\"Membership\",\"compensation\":20.00}]}]"
        }
    ]
}

How can I fix this, from either the query, or Node?

Thanks.

CodePudding user response：

As AlwaysLearning said, try

let sqlString = `
select (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 = ${userData.plid}) for json path) as data`

This will result in

 {
        "success": 1,
        "data": [
            {
                "data": "[{\"codeid\":1,\"code\":\"pablo\",\"validFrom\":\"2020-01-01\",\"validTo\":\"2022-01-01\",\"products\":[{\"productIdentifier\":\"Sigma\",\"productName\":\"Sigma Tramma\",\"compensation\":28.00},{\"productIdentifier\":\"membership\",\"productName\":\"Membership\",\"compensation\":30.00}]},{\"codeid\":2,\"code\":\"paola20\",\"validFrom\":\"2021-01-01\",\"validTo\":\"2020-01-01\",\"products\":[{\"productIdentifier\":\"Sigma\",\"productName\":\"Sigma Tramma\",\"compensation\":18.00},{\"productIdentifier\":\"membership\",\"productName\":\"Membership\",\"compensation\":20.00}]}]"
            }
        ]
    }

Then you can

let parsedResponse=JSON.parse(apiResponse.data[0].data);
console.log(parsedResponse)

Hope it helped.