Example code for converting Mysql query result set into JSON data

Mysql converts query result set into JSON data Preface Student table Student score table Query the scores of each subject of a single student (convert to object JSON string and concatenate with commas) Convert the scores of each subject of a single student into an array JSON string Use the array string as value and set the key Joint query of two tables (final SQL, each student's score in each subject) Final result

Preface

We often have such a requirement, a pair of related tables, a one-to-many relationship, use a SQL statement to query all the records of the two tables, for example: a student table, a student's score table, we want to use a SQL statement to query the scores of each student in each subject;

Student Table

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

Student transcript

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

Query the scores of each subject of a single student (convert to object JSON string and concatenate with commas)

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"}

Convert a single student's grades into an array JSON string

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"}]

Use the array string as value and set the key

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"}]}

Joint query of two tables (final SQL, each student's grades in each subject)

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"}]

Final Result

ID	NAME	SCORES
1	Zhang San	[{“id”: 1, “name”: “数学”, “student_id”: 1, “score”: “95.5”},{“id”: 2, “name”: “语文”, “student_id”: 1, “score”: “99.5”}]
2	Li Si	[{“id”: 3, “name”: “Mathematics”, “student_id”: 1, “score”: “95.5”},{“id”: 4, “name”: “Chinese”, “student_id”: 1, “score”: “88”}]

This is the end of this article about converting MySQL query result sets to JSON data. For more relevant MySQL result set conversion to JSON data, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in: