How to output result of a mysql query as a json file in node.js

Question

What i have her is this

app.post('/login', function (req, res){
  connection.connect();
  email = req.body.email;
  password = req.body.password;
  if(email && password ) {
    console.log(email);
    console.log(password);
   // connection.query('SELECT username FROM user WHERE email =? and password = ?', [email, password], 
   // function (error, rows, fields) { 
    connection.query('SELECT * FROM user ', 
    function (error, rows, fields) {
      var str='';
      for (var i = 0;i < rows.length; i++) {
        str = str + rows[i].username;
        res.end(str);
      }
      connection.end(); 
    }); 
  }
});

so instead of just displaying the result i want it to be something like this:

{
 "username": "ABC",
 "username": "DEF",
 "username": "HIJ"
}

Can you please clarify? which query are you trying to execute? It seems like the commented out query is the one you want? And the output you want isn't possible. You cant have the same key on a single object. — majidarif
– majidarif, Commented Oct 15, 2014 at 11:30
Makes more sense, but the output you are expecting is not possible. an array of usernames like ['ABC', 'DEF', 'HIJ'] can work though. — majidarif
– majidarif, Commented Oct 15, 2014 at 11:39
could you please show me how to do that,i tried JSON.stringify(str) it just puts the str between double quotes now i want it as an array — Rawan Hamdi
– Rawan Hamdi, Commented Oct 15, 2014 at 11:45

Mike Perrenoud · Accepted Answer · 2014-10-15 12:01:37Z

11

The first problem is you're not actually building the object you want. The second problem is you're not building an array of them. Finally, you need to convert that array into JSON if the res object doesn't have something like res.json.

var objs = [];
for (var i = 0;i < rows.length; i++) {
    objs.push({username: rows[i].username});
}
connection.end();
res.end(JSON.stringify(objs));

answered Oct 15, 2014 at 12:01

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2016-12-23 21:53:36Z

-3

With Express 4.x, the output rows from mysql db is in json format.

For example, look at this

sqlConnect.connection.query('SELECT * from users', function(err, rows, fields){
		if (err)
		{
			console.log("Querying error");
		}
		else
		{
			console.log(rows);
		}
		sqlConnect.connection.end();
	});
});

The output is of the form

[ RowDataPacket {
    id: 1,
    username: 'shivkanth',
    password: '1q2w3e4r',
    verified: 0 } ]

So now you can get the individual values using the . operator. For example, console.log(rows[0].username) will log the value 'shivkanth'

edited Dec 23, 2016 at 21:53

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 17, 2015 at 9:06

Shivkanth

1332 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to output result of a mysql query as a json file in node.js

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related