javascript - MySQL query returns a string as a result of JSON_OBJECT() - Stack Overflow

admin管理员组

文章数量:1332329

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 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)
};

• javascript
• mysql
• json
• node.js
• express

Share Improve this question Follow edited May 3, 2017 at 12:37 rel1x asked May 3, 2017 at 10:40 rel1xrel1x 2,44144 gold badges3636 silver badges6868 bronze badges 5

• 2 Probably something specific to the programming language you are using. What is it BTW? – e4c5 Commented May 3, 2017 at 10:42
• I use javascript – rel1x Commented May 3, 2017 at 10:44
• 1 I am not a node expert but my understanding is that it has multiple different ways of connecting to an RDBMS sadly what you have chosen does not seem to support json objects. JSON after all is a some what recent addition to mysql – e4c5 Commented May 3, 2017 at 10:45
• 1 I agree with @e4c5 , this is probably due to how json objects returned by MySQL are handled by the client. You probably have to explicitly convert the string to json in js code. – Shadow Commented May 3, 2017 at 11:04
• @Shadow I already do that. I updated my post and added a function from the client that touch server. – rel1x Commented May 3, 2017 at 11:22

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 4

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...

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();
    },
    ..
  }

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

本文标签： javascriptMySQL query returns a string as a result of JSONOBJECT()Stack Overflow