NodeJS, MySQL - JSON Stringify - Advanced query
Unless you are querying programmatically where you can parse the JSON and then do the logic, I would recommend something like this:
SELECT * FROM Table WHERE Column LIKE '%"id": 1,"uID": 10%'
The LIKE
keyword allows us to use wildcards (%) but still do an exact text match for what we define.
mysql JSON arrays returned as string
You can convert strings to Objects or Arrays with JSON.parse()
. You also can reverse this operation with JSON.stringify()
. Here's your code with these minor changes.
mysql.getConnection((err, con) => {
if(err) return console.log(err);
con.query(`SELECT * FROM guild WHERE ID = "1" LIMIT 1`, (err, guildStr) => {
if(err) return console.log(err);
const guild = JSON.parse(guildStr)
console.log(guild)
})
})
I don't know how to store an array of object with mysql/node correctly
So i made it!!!
I registered array of objects (which was stringified) as a text format in mysql workbench!
it worked!!
this.$axios.post('http://localhost:3000/api/list/', {
'user_id': this.$auth.user[0].user_id,
'to_uuid': this.$store.state.list.to_user.user_id,
'content': JSON.stringify(this.$store.state.list.finalList),
'message': this.message,
'list_name': this.name,
'status': "en attente"
})
this part is when i post the stringified array on content
async created(){
const listReceived = await this.$axios.$get(`/api/listReceived/${this.$auth.user[0].user_id}`)
const usersList = await this.$axios.$get("/api/users")
listReceived.forEach(element => {
const userdata=usersList.filter((post)=>post.user_id===element.user_id)
const userSelected={
listId:element.list_id,
listName:element.list_name,
message:element.message,
status:element.status,
content: JSON.parse(element.content),
nickname:userdata[0].nickname
}
this.finalList.push(userSelected)
})
this part is when i get the stringified array - I JSON.parse it on content
How do I solve "TypeError: Converting circular structure to JSON" in NodeJS
Circular JSON means, there is a reference to an object inside the object, which makes the JSON.stringify not possible.
in your case, res.send(JSON.stringify(answer)) could be the problem. The response of con.query(sql) may not be a simple object.
Also, the function is asynchronous and expects a callback to get the answer (I am assuming you are using mysqljs https://github.com/mysqljs/mysql)
give more details to explain further, but the underlying problem is the value of the variable "answer"
Mysql dynamic query in node.js
Create a new route in your application. You can specify parameters in your URL as
http://xxx.xxx.xx.x:3002/users/fname1
Here fname1 is user Id and you can add the following code to get the parameter from url
req.params.fname
Here the fname is got the name from the route that defined in your application as:
app.get('/users/:fname', function (req, res) {
connection.query("select * from nodejs where fname= '"+req.params.fname+"'", function(err, rows, fields) {
res.writeHead(200, { 'Content-Type': 'application/json'});
res.end(JSON.stringify(rows));
//res.render('users', {users: docs});
});
});
Use url/:nameOfParameter to specify the name for values in url
Smarter way to create JSON response from Mysql
try MongoDB database (mongodb.org) .MongoDB is nosql document database, it solves problems like this very easy. And one more point. MongoDB is default database for nodejs. It works with javascript queries.
Related Topics
Getting Two Counts and Then Dividing Them
Sql Server - Join Rows into Comma Separated List
What Is and How to Remove Tablespace Error from My Database
Regex Pattern Inside SQL Replace Function
Sql71501 - How to Get Rid of This Error
Phpmyadmin Says No Privilege to Create Database, Despite Logged in as Root User
Mysql Workbench Edit Table Data Is Read Only
Select Rows Within Last Complete Minute
Nodejs, MySQL - Json Stringify - Advanced Query
How to Store Emoji Character in MySQL Database
How to Minus Current and Previous Value in SQL Server
Postgresql - Combining Multiple Rows With Several Attributes into One Row
Select Multiple Columns Count One Column and Group by One Column in One Table
Joining Tables from Different Servers
Get Last Record of a Table in Postgres
Sql: Update Column With Increment Numbers Based on 2 Columns