Node, Express, MYSQL Retrieving JSON from MYSQL

Question

I have stored JSON in a database like under the accesssPoint field. It looks exactly like this:

{"mode": "client", "rate": 0, "ssid": "RMG", "signal": 0, "channel": 0, "password": "", "username": "[email protected]"}

I run a MYSQL query from within Node and for some reason I get this as output:

[{"accessPoint":"[{\"mode\": \"client\", \"rate\": 0, \"ssid\": \"RMG\", \"signal\": 0, \"channel\": 0, \"password\": \"\", \"username\": \"[email protected]\"}]"}]

when I use:

db.query('SELECT accessPoint FROM player_r WHERE id=?', [playerID], function (err, results) {
         var secondQuery = [];

         results.forEach(function (item) {
         secondQuery.push(item);
         });

                res.send(results);
            });

Could someone please help me to extract this JSON the correct way? Help would be GREATLY appreciated!

clay · Accepted Answer · 2016-12-06 18:32:24Z

1

The results that came back are an array of objects, where each object's only property is "accessPoint". That value of that property is the JSON string your stored in the database. You'll need to JSON.parse to get some values.

You could also use a library like knexJS to make calls to the database. The docs are great, the results are JSON objects, and it likely can use the same SQL driver library you're using right now.

MySQL also has a specific JSON datatype but you would need to use that query syntax rather than just a plain SELECT.

answered Dec 6, 2016 at 18:32

clay

6,0172 gold badges26 silver badges24 bronze badges

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

5 Comments

Stephen Kuehl Over a year ago

Thank you for you quick reply. I dont think it has too much to do with the way I query the JSON from MYSQL but I might be wrong. When I do a console.log of the results, there are no added slashes. However, when I do a res.send or res.json, I get the added slashes. Whenever I try to JSON.parse the results, I get an error in my console (Unexpected token o in JSON at position 1).

clay Over a year ago

You should try a typeof on your raw response. When you console log, is it JSON (typeof results.accesspoint === 'object') or does it just look like JSON (typeof results.accesspoint === 'string')?

Stephen Kuehl Over a year ago

console.log(typeof results === 'string') console.log(typeof results === 'object') console.log(typeof results === 'array') console.log(typeof results.accessPoint === 'object') console.log(typeof results.accessPoint === 'string') console.log(typeof results.accessPoint === 'array') I ran all of the following and they were all false expect for console.log(typeof results === 'object')

Stephen Kuehl Over a year ago

parsed = JSON.stringify(results); res.status(500).json(parsed); Creates extra slashes in the res.send response that looks like this:

"[{\"accessPoint\":\"[{\\\"mode\\\": \\\"client\\\", \\\"rate\\\": 0, \\\"ssid\\\": \\\"RMG\\\", \\\"signal\\\": 0, \\\"channel\\\": 0, \\\"password\\\": \\\"\\\", \\\"username\\\": \\\"[email protected]\\\"}]\"}]"

clay Over a year ago

not results, what is the type of results[0].accesspoint (i.e The first object in the results array, and the property accesspoint of that object). Also, if you did var point = results[0].accesspoint;, what is the typeof of that, and what does console.log(point) print out?

Collectives™ on Stack Overflow

Node, Express, MYSQL Retrieving JSON from MYSQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related