Wednesday, 3 May 2017

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?



via rel1x

No comments:

Post a Comment