CODEWITHSUNDEEP
javascriptmysqljsonnode.jsexpress
rel1x
rel1x

Reputation: 2441

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

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

Upvotes: 2

Views: 5724

Answers (3)

Kiran S
Kiran S

Reputation: 423

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

Upvotes: 2

Johnni Mortensen
Johnni Mortensen

Reputation: 1

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

Upvotes: 0

Stefano Zanini
Stefano Zanini

Reputation: 5916

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

Upvotes: 3

Related Questions