Btukfyl

I am working on a creating a nodejs express API querying a Vertica database using node-vertica. I can return the results but how do I convert/serialize the results in json format? Right now the results are being returned fields and rows. Also when I comment out my connection close the results of the query are no longer displayed in the browser. Here is the connection/query I have. Any help is greatly appreciated and if there is a better more supported module than node-vertica please let me know.

var express = require('express');

var router = express.Router();

Vertica = require('vertica');



var dbConfig = require('../secrets/dbconfig.js');



var config = {

  ssl: 'optional',

  interruptible: true,

  host: 'xxxx',

  user: dbConfig.iqi_user,

  password: dbConfig.iqi_password,

  database: dbConfig.iqi_DBname

};



try {

  conn = Vertica.connect(config, (err, conn) => {

    if (err) {

      console.log('error');

    } else {

      //console.log(conn);



      router.get('/', (req, res, next) => {

        conn.query('SELECT * FROM LTE_USID_CQI_2018 LIMIT 5', (err, result) => {

          if (err) throw 'hello' + err;

          res.send(result);

        });

      });

      //conn.disconnect();

    }

  });

}

catch (error) {

  console.log("Error has been caught");

  console.log(error);

}



module.exports = router;

sample output:

{

   "fields":[

      {

         "name":"PERIOD_START_DATE",

         "tableOID":982487324,

         "tableFieldIndex":1,

         "typeOID":10,

         "type":"date",

         "size":8,

         "modifier":4294967295,

         "formatCode":0

      }

   ],

   "rows":[

      [

         "2018-01-01",

         "2018-01-01",

         "Arkansas/Oklahoma",

         "Arkansas",

         14465,

         -1.666162554,

         -0.000055538882,

         -0.00022931,

         -0.001442423085,

         -13.171986306,

         -72.151515449,

         -48.595225949,

         394158,

         396860,

         2905,

         396237,

         397289,

         397733.4534914,

         2968127857,

         590818.977,

         102111.1,

         854609.1,

         133655950,

         1606446,

         27898.3106060606

      ]

   ],

   "notices":[



   ],

   "status":""

}

Hi rf guy and sorry for the late reply

Here is a snippet that transform the results to a standard json.

const res = {

  "fields": [{

    "name":" PERIOD_START_DATE"

  }, {

    "name": "STATE"

  }, {

    "name": "VALUE"

  }],

  "rows": [

    [

      "2018-01-01",                  

      "Arkansas",

      11111,         

    ], [

      "2019-01-01",                  

      "Oklahomo",

      22222,         

    ]

  ]

}

 

function mapToJSON(dbResult) {

  const fieldNames = dbResult.fields.map(field => field.name) // List of all field names

    

  return dbResult.rows.map(row => {  	

    return row.reduce((obj, item, index) => {

      const header = fieldNames[index]

      obj[header] = item

      return obj

    }, {})    

   })

}



const ans = mapToJSON(res)



console.log(ans)

  

In your code you could do something like

res.send(mapToJSON(result));

Or even better, since you're using Express

res.json(mapToJSON(result));

Best regards
Bergur