I wrote a query that gives me posts from a table and also returns an info about each post's author:
SELECT post.id, post.text, post.datetime, JSON_OBJECT(
'username', user.username,
'firstName', user.firstName,
'firstName', user.lastName) as author
FROM post
INNER JOIN user ON post.authorId = user.id;
But in response the author field is a string:
author: "{"username": "@", "firstName": null}"
datetime: "2017-05-02T20:23:23.000Z"
id: 10
text: "5555"
I tried to fix that using CAST but anyway author is a string:
CAST(JSON_OBJECT(
'username', user.username,
'firstName', user.firstName,
'firstName', user.lastName) as JSON) as author
Why is it happened and how to fix that?
UPD:
I send the data from server using Node.js and Express:
app.get('/posts', (req, res, next) => {
getPosts().then((posts) => {
res.setHeader('Content-Type', 'application/json');
res.send(posts);
})
.catch(next);
});
// ...
getPosts() {
return new Promise((resolve, reject) => {
const query = `
SELECT post.id, post.text, post.datetime, JSON_OBJECT(
'username', user.username,
'firstName', user.firstName,
'firstName', user.lastName) as author
FROM post
INNER JOIN user ON post.authorId = user.id;`;
this.connection.query(query, (err, result) => {
if(err) {
return reject(new Error("An error occured getting the posts: " + err));
}
console.log(result) // prints author as a string
resolve(result || []);
});
});
}
Result of console.log:
{
id: 1,
text: 'hello, world!',
datetime: 2017-05-02T15:08:34.000Z,
author: '{"username": "@", "firstName": null}'
}
I also tried here change res.send(posts) to res.json(posts) but it's doesn't help.
My function from client that touch server for the posts:
export const getPosts = () => {
customFetch(apiUrl + '/posts')
.then(response => response.json())
.then(json => json)
};
I think it's fine for MySQL to return a string, as the JSON_OBJECT() function is already doing its job by producing a string that represents a well formatted JSON.
You can convert that string to a JSON object in javascript with
var obj = JSON.parse(yourString);
Edit (about JSON and Javascript objects)
First of all, in case you didn't know, JSON stands for JavaScript Object Notation: that means that it's a textual way of representing JavaScript objects.
From MySQL point of view, you're already solving this problem inside the SELECT statement, because what the query is returning is a valid JSON.
The fact is that then that data is transmitted to Javascript (Node), but Javascript internal representation of an object is not the same as its textual representation (the JSON); this means you have to "cast" it, so that the string gets converted to the object.
The mechanism you'd need in order to avoid this cast would require MySQL to know how Javascript represents an object, and then using such knowledge to return the bytecode of your object. This is called serialization, and I'm afraid it's way beyond the purpose of a dbms like MySQL.
Hope this clarifies your doubts a bit...
What about returning the whole thing as a JSON_OBJECT and doing JSON.Parse on it once
SELECT JSON_OBJECT("id", post.id, "text", post.text, "datetime", post.datetime, "author", JSON_OBJECT(
'username', user.username,
'firstName', user.firstName,
'firstName', user.lastName))
FROM post
INNER JOIN user ON post.authorId = user.id;
That eliminates your need to loop
Used JSON typeCast in the DB config file
connection: {
..
host: process.env.DB_HOST,
user: process.env.DB_USER,
..
typeCast: function (field, next) {
if (field.type == 'JSON') {
return (JSON.parse(field.string()));
}
return next();
},
..
}