CODEWITHSUNDEEP
javascriptjsonsql-servernode.js
Jay
Jay

Reputation: 9509

Parsing JSON result from Nodejs mssql result

I have a NodeJS app calling a MSSqlserver stored procs and returns a JSON result.

How do I parse the JSON result to get only this value

[{"DataMartCode":"NPL","DataMartDescription":null},{"DataMartCode":"FP","DataMartDescription":"Only for Banks that report Funding Plans"}]

JSON result

{
    recordsets:[
        [
            {
                JSON_F52E2B61-18A1-11d1 - B105-00805F49916B:"[{"DataMartCode":"NPL","DataMartDescription":null},{"DataMartCode":"FP","DataMartDescription":"Only for Banks that report Funding Plans"}]"
            }
        ]
    ], 
    recordset:[ 
        {
            JSON_F52E2B61-18A1-11d1 - B105-00805F49916B:"[{"DataMartCode":"NPL","DataMartDescription":null},{"DataMartCode":"FP","DataMartDescription":"Only for Banks that report Funding Plans"}]"
        }
    ], 
    output: {}, 
    rowsAffected:[11], 
    returnValue:0
}

Javascript

var express = require('express'); 
var router = express.Router(); 
var sql = require('mssql'); 

/* GET home page. */
router.get('/', function(req, res, next) {
    res.render('index',  {title:'Express'}); 
}); 


// SQL Server config
var config =  {
    user:'user', 
    password:'pass', 
    server:'localhost', 
    database:'mydb'
}; 


router.get('/datamart', function (req, res) {

    // connect to your database
    sql.connect(config, function (err) {
        if (err)console.log(err); 

        // create Request object
        var request = new sql.Request(); 

        // query to the database and get the data
        request.execute('dbo.get_datamart', function (err, result) {
            if (err)console.log(err)

            // send data as a response
            console.log(result.recordset);
            res.send(result); 

            sql.close();
        }); 

    }); 

}); 

module.exports = router;

Upvotes: 0

Views: 7020

Answers (2)

user890332
user890332

Reputation: 1361

There's an easier way than @amit answered.

let employees = [];
for (let i = 0; i < data.rowsAffected; i++) {
    employees.push(data.recordset[i]);
}

That way you don't have to use FOR JSON PATH

Upvotes: 0

Amit Wagner
Amit Wagner

Reputation: 3274

note that your json is not a valid json and i had to fix it manually. also you can upgrade my answer with recursion that check if array has array in it and so on. but this will work to 

results = {
    recordsets:[
        [
            {
                "JSON_F52E2B61-18A1-11d1 - B105-00805F49916B":[{"DataMartCode":"NPL","DataMartDescription":null},{"DataMartCode":"FP","DataMartDescription":"Only for Banks that report Funding Plans"}]
            }
        ]
    ], 
    recordset:[ 
        {
            "JSON_F52E2B61-18A1-11d1 - B105-00805F49916B":[{"DataMartCode":"NPL","DataMartDescription":null},{"DataMartCode":"FP","DataMartDescription":"Only for Banks that report Funding Plans"}]
        }
    ], 
    output: {}, 
    rowsAffected:[11], 
    returnValue:0
}
let newResults = [];
for(let key in results){
   if(key === "recordsets"){
      results[key].forEach(arr =>{
        arr.forEach(obj =>{
            Object.keys(obj).forEach((key) =>{
              newResults.push(obj[key])
            })
          });
      })
   }
}
console.log(newResults)

Upvotes: 3

Related Questions