MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE comments (
  comment_id     INTEGER,
  news_id        INTEGER,
  comment_names  VARCHAR(200),
  comment_date   TIMESTAMP,
  comments       TEXT
  );
CREATE TABLE comments_reply (
  reply_id       INTEGER,
  comment_id     INTEGER,
  reply_names    VARCHAR(200),
  reply          TEXT,
  reply_date     TIMESTAMP
  );
INSERT INTO comments VALUES
  (1,1,'Comment1','2023-01-01 10:00:00', 'This is the first comment'),
  (2,1,'Comment2','2023-01-02 11:00:00', 'This is the second comment'),
  (3,1,'Comment3','2023-01-03 12:00:00', 'This is the third comment');

Records: 3  Duplicates: 0  Warnings: 0


SELECT * FROM comments ;


comment_id
news_id
comment_names
comment_date
comments


1
1
Comment1
2023-01-01 10:00:00
This is the first comment

2
1
Comment2
2023-01-02 11:00:00
This is the second comment

3
1
Comment3
2023-01-03 12:00:00
This is the third comment


INSERT INTO comments_reply VALUES
   (1,1,'ReplyName1','Reply1','2023-01-01'),
   (2,2,'ReplyName2','Reply2','2023-01-02'),
   (3,3,'ReplyName3','Reply3','2023-01-03');

Records: 3  Duplicates: 0  Warnings: 0


SELECT * FROM comments_reply ;


reply_id
comment_id
reply_names
reply
reply_date


1
1
ReplyName1
Reply1
2023-01-01 00:00:00

2
2
ReplyName2
Reply2
2023-01-02 00:00:00

3
3
ReplyName3
Reply3
2023-01-03 00:00:00


SELECT 
  c.*, 
  (
  SELECT json_arrayagg(json_object(
      'id',cr.comment_id,
      'reply_date',date(cr.reply_date),
      'reply_time',time(cr.reply_date),
      'names',cr.reply_names,
      'reply',cr.reply
     ))
  FROM comments_reply cr
  WHERE cr.comment_id = c.comment_id
  ) cr
FROM comments c
WHERE c.news_id = 1


comment_id
news_id
comment_names
comment_date
comments
cr


1
1
Comment1
2023-01-01 10:00:00
This is the first comment
[{"id": 1, "names": "ReplyName1", "reply": "Reply1", "reply_date": "2023-01-01", "reply_time": "00:00:00.000000"}]

2
1
Comment2
2023-01-02 11:00:00
This is the second comment
[{"id": 2, "names": "ReplyName2", "reply": "Reply2", "reply_date": "2023-01-02", "reply_time": "00:00:00.000000"}]

3
1
Comment3
2023-01-03 12:00:00
This is the third comment
[{"id": 3, "names": "ReplyName3", "reply": "Reply3", "reply_date": "2023-01-03", "reply_time": "00:00:00.000000"}]


SELECT json_arrayagg(json_object(
  'id',x.comment_id,
  'comment_date',date(x.comment_date),
  'comment_time',time(x.comment_date),
  'names',x.comment_names,
  'comments', x.comments,
  'replies',x.cr
  )) as JSON
FROM (
SELECT 
  c.*, 
  (
  SELECT json_arrayagg(json_object(
      'id',cr.comment_id,
      'reply_date',date(cr.reply_date),
      'reply_time',time(cr.reply_date),
      'names',cr.reply_names,
      'reply',cr.reply
     ))
  FROM comments_reply cr
  WHERE cr.comment_id = c.comment_id
  ) cr
FROM comments c
WHERE c.news_id = 1
) x


JSON


[{"id": 1, "names": "Comment1", "replies": [{"id": 1, "names": "ReplyName1", "reply": "Reply1", "reply_date": "2023-01-01", "reply_time": "00:00:00.000000"}], "comments": "This is the first comment", "comment_date": "2023-01-01", "comment_time": "10:00:00.000000"}, {"id": 2, "names": "Comment2", "replies": [{"id": 2, "names": "ReplyName2", "reply": "Reply2", "reply_date": "2023-01-02", "reply_time": "00:00:00.000000"}], "comments": "This is the second comment", "comment_date": "2023-01-02", "comment_time": "11:00:00.000000"}, {"id": 3, "names": "Comment3", "replies": [{"id": 3, "names": "ReplyName3", "reply": "Reply3", "reply_date": "2023-01-03", "reply_time": "00:00:00.000000"}], "comments": "This is the third comment", "comment_date": "2023-01-03", "comment_time": "12:00:00.000000"}]

comment_id	news_id	comment_names	comment_date	comments
1	1	Comment1	2023-01-01 10:00:00	This is the first comment
2	1	Comment2	2023-01-02 11:00:00	This is the second comment
3	1	Comment3	2023-01-03 12:00:00	This is the third comment

reply_id	comment_id	reply_names	reply	reply_date
1	1	ReplyName1	Reply1	2023-01-01 00:00:00
2	2	ReplyName2	Reply2	2023-01-02 00:00:00
3	3	ReplyName3	Reply3	2023-01-03 00:00:00