nodejs vertica return query as json

Question

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":""
}

Answer 1

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