MySQL query returns a string as a result of JSON_OBJECT()

Question

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

Probably something specific to the programming language you are using. What is it BTW? — e4c5
– e4c5, Commented May 3, 2017 at 10:42
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
– e4c5, Commented May 3, 2017 at 10:45
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
– 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
– rel1x, Commented May 3, 2017 at 11:22

Stefano Zanini · Accepted Answer · 2017-05-03 13:56:45Z

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

edited May 3, 2017 at 13:56

answered May 3, 2017 at 11:05

Stefano Zanini

5,9362 gold badges15 silver badges33 bronze badges

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

4 Comments

rel1x Over a year ago

I do that response => response.json(). Or you mean I have to look for each key in my json that can be also json and use JSON.parse for them too?

Stefano Zanini Over a year ago

Yes, the whole response won't be a valid JSON, but the author field surely will.

rel1x Over a year ago

Okay, I get data from the table, iterate over data and modify each author using JSON.parse(post.author) and send it to the client. That works fine now, but it's looks so strange. Is there really no way to solve a problem inside of SELECT?

Stefano Zanini Over a year ago

I edited my answer, it was way to long for a comment :)

Kiran S · Accepted Answer · 2020-10-06 13:02:32Z

2

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

answered Oct 6, 2020 at 13:02

Kiran S

4218 silver badges15 bronze badges

1 Comment

bahadir arslan Over a year ago

Thank you, this is the right choice if your result contains both fields and JSON_OBJECT.

Johnni Mortensen · Accepted Answer · 2017-12-01 10:48:01Z

0

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

answered Dec 1, 2017 at 10:48

Johnni Mortensen

11 silver badge3 bronze badges

Collectives™ on Stack Overflow

MySQL query returns a string as a result of JSON_OBJECT()

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related