Example code for converting Mysql query result set into JSON data

CREATE TABLE IF NOT EXISTS `student`(
 `id` INT UNSIGNED AUTO_INCREMENT,
 `name` VARCHAR(100) NOT NULL
 PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO student( id, name ) VALUES ( 1, '张三' );
INSERT INTO student( id, name ) VALUES ( 2, 'Li Si' );

CREATE TABLE IF NOT EXISTS `score`(
 `id` INT UNSIGNED AUTO_INCREMENT,
 `name` VARCHAR(100) NOT NULL
 `student_id` INT(100) NOT NULL,
 `score` VARCHAR(100) NOT NULL
 PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO score( id, name, student_id, score) VALUES ( 1, 'Mathematics', 1, '95.5' );
INSERT INTO score( id, name, student_id, score) VALUES ( 2, 'Chinese', 1, '99.5' );
INSERT INTO score( id, name, student_id, score) VALUES ( 3, 'Mathematics', 2, '95.5' );
INSERT INTO score( id, name, student_id, score) VALUES ( 4, 'Chinese', 2, '88' );

SELECT GROUP_CONCAT(JSON_OBJECT( 
'id',id,'name',name,'student_id',student_id, 'score', score)) as scores FROM score where student_id = 1;
## Query results## {"id": 1, "name": "Mathematics", "student_id": 1, "score": "95.5"},{"id": 2, "name": "Chinese", "student_id": 1, "score": "99.5"}

SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT( 
'id',id,'name',name,'student_id',student_id, 'score', score)), ']') as scores FROM score where student_id = 1
## Query results## [{"id": 1, "name": "Mathematics", "student_id": 1, "score": "95.5"},{"id": 2, "name": "Chinese", "student_id": 1, "score": "99.5"}]

SELECT CONCAT('{"scoreData":[', GROUP_CONCAT(JSON_OBJECT( 
'id',id,'name',name,'student_id',student_id, 'score', score)), ']}') as scores FROM score where student_id = 1
## Query results## {"scoreData": [{"id": 1, "name": "Mathematics", "student_id": 1, "score": "95.5"},{"id": 2, "name": "Chinese", "student_id": 1, "score": "99.5"}]}

SELECT id, name,
(SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT( 
'id',id,'name',name,'student_id',student_id, 'score', score)), ']') as scores FROM score WHERE student_id = stu.id) AS scores
from student stu
## [{"id": 1, "name": "Mathematics", "student_id": 1, "score": "95.5"},{"id": 2, "name": "Chinese", "student_id": 1, "score": "99.5"}]