MySQL generate a sublist of comment in a query

I have a database of 3 tables Question, Answer, Comment it works as it is supposed to, each Question can have multiple Answers and each Answer can have multiple Comments

create table Question(
    questionId int primary key auto_increment,
    title varchar(34) not null,
    content TEXT not null
);

create table Answer(
    answerId int primary key auto_increment,
    questionId int not null,
    content TEXT not null
);

create table Comment(
    commentId int primary key auto_increment,
    answerId int,
    questionId int,
    content TEXT not null
);

insert into Question(title, content) values('Testing Question','Testing Question');

insert into Answer(questionId, content) values(1,'Testing Answer 1');
insert into Answer(questionId, content) values(1,'Testing Answer 2');

insert into Comment(answerId, questionId, content) values(1, 1,'Testing Comment 1 to Answer 1');
insert into Comment(answerId, questionId, content) values(1, 1,'Testing Comment 2 to Answer 1');
insert into Comment(answerId, questionId, content) values(2, 1,'Testing Comment 1 to Answer 2');
insert into Comment(answerId, questionId, content) values(2, 1,'Testing Comment 2 to Answer 2');

I would like when i query an answer and its comments i get the comments in a List such as:

[
{ answerId: 1, answerContent: 'Answer Content', comments[...]},
{ answerId: 2, answerContent: 'Answer Content', comments[...]}
]

Is this possible? i have tried to use this query:

select * from Answer inner join Comment on Comment.answerId = Answer.answerId where Comment.questionId = 1;

And obviously it gives me back this:

[
    {
        "answerId" : 1,
        "questionId" : 1,
        "content" : "Testing Answer 1",
        "commentId" : 1,
        "answerId" : 1,
        "questionId" : 1,
        "content" : "Testing Comment 1 to Answer 1"
    },
    {
        "answerId" : 1,
        "questionId" : 1,
        "content" : "Testing Answer 1",
        "commentId" : 2,
        "answerId" : 1,
        "questionId" : 1,
        "content" : "Testing Comment 2 to Answer 1"
    },
    {
        "answerId" : 2,
        "questionId" : 1,
        "content" : "Testing Answer 2",
        "commentId" : 3,
        "answerId" : 2,
        "questionId" : 1,
        "content" : "Testing Comment 1 to Answer 2"
    },
    {
        "answerId" : 2,
        "questionId" : 1,
        "content" : "Testing Answer 2",
        "commentId" : 4,
        "answerId" : 2,
        "questionId" : 1,
        "content" : "Testing Comment 2 to Answer 2"
    }
]

I have tried also using the group by in the previous query like this:

select * from Answer inner join Comment on Comment.answerId = Answer.answerId where Comment.questionId = 1 group by Comment.questionId;

And it gives me back this:

[
    {
        "answerId" : 1,
        "questionId" : 1,
        "content" : "Testing Answer 1",
        "commentId" : 1,
        "answerId" : 1,
        "questionId" : 1,
        "content" : "Testing Comment 1 to Answer 1"
    }
]

Answer

You can use json_arrayagg():

select a.*, json_arrayagg(c.content)
from Answer a inner join
     Comment c
     on c.answerId = a.answerId
where c.questionId = 1
group by a.answerId

Here is a db<>fiddle.

If you want all rows, even those with no comments, you can use:

select a.*, json_arrayagg(c.content)
from Answer a left join
     Comment c
     on c.answerId = a.answerId and
        c.questionId = 1
group by a.answerId

Advertisement

Answer