How to convert result table to JSON array in MySQL

In MySQL, you can use the JSON_ARRAYAGG() function to convert query results into a JSON array. This function aggregates a set of values into a JSON array. Additionally, you can use the JSON_OBJECT() function to create JSON objects, allowing for more flexible definition of key names and values. I will demonstrate how to use these functions with a specific example.

Assume we have a table named students with the following fields: id, name, age. Our goal is to convert the query results into a JSON array containing multiple student records.

Example Data Table students

id	name	age
1	Alice	22
2	Bob	24
3	Charlie	21

We want to obtain a JSON array where each element is a JSON object containing student information:

sql
SELECT JSON_ARRAYAGG(
         JSON_OBJECT(
             'id', id,
             'name', name,
             'age', age
         )
     ) AS students_json
FROM students;

After executing the above query, the result will be:

json
[
    {"id": 1, "name": "Alice", "age": 22},
    {"id": 2, "name": "Bob", "age": 24},
    {"id": 3, "name": "Charlie", "age": 21}
]

In this example, the JSON_OBJECT() function is used to create the JSON representation for each student, including the keys id, name, and age with their corresponding values. Then, the JSON_ARRAYAGG() function aggregates all these JSON objects into an array.

This approach enables convenient generation of JSON-formatted data directly at the database level, which is ideal for decoupled frontend-backend applications. It allows sending this formatted data directly to the frontend, thereby reducing the processing burden on the backend.