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:

Mysql directly queries the data in the stored Json string
MySQL json format data query operation
Python query mysql, return json instance
Store JSON strings in Mysql and query according to conditions

<<: Semantic web pages XHTML semantic markup

>>: Docker completely deletes private library images

Example code for converting Mysql query result set into JSON data

Some tips on website design

Tomcat parses XML and creates objects through reflection

Practical method of deleting a row in a MySql table

Vue's vue.$set() method source code case detailed explanation

CSS easily implements fixed-ratio block-level containers

How to use environment variables in nginx configuration file

HTML tag marquee realizes various scrolling effects (without JS control)

How to create scheduled tasks using crond tool in Linux

Solution to MySQL server login error ERROR 1820 (HY000)

Solution to primary key conflict when innodb_index_stats reports an error when importing backup data

Recommend

Select web page drop-down list and div layer covering problem

Example code for implementing random roll caller in html

Summary of special processing statements of MySQL SQL statements (must read)

A case study on MySQL optimization

The difference between two MySQL delete user statements (delete user and drop user)

Start nginxssl configuration based on docker

Detailed explanation of the binlog log analysis tool for monitoring MySQL: Canal

MySQL foreign key setting method example

MySql inserts data successfully but reports [Err] 1055 error solution

Detailed explanation of common commands for network configuration of containers in Docker

4 solutions to CSS browser compatibility issues

Docker implements MariaDB sub-library and sub-table and read-write separation functions

MySQL learning database backup detailed explanation

VMware Workstation 14 Pro installation and activation graphic tutorial

React antd realizes dynamic increase and decrease of form