MySQL 8 查询表返回 JSON 数据：使用 JSON_OBJECT、JSON_ARRAYAGG 和 JSON_TABLE 函数

在 MySQL 8 中，可以使用 JSON_OBJECT、JSON_ARRAYAGG 和 JSON_TABLE 函数来查询表并返回 JSON 格式的结果。

例如，假设有一个表格名为 'students'，其中有 'id'、'name' 和 'age' 三列，我们要查询所有学生的信息并返回 JSON 格式的结果，可以使用以下查询语句：

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

这将返回一个 JSON 数组，其中包含每个学生的信息，如下所示：

[
    {'id': 1, 'name': 'Tom', 'age': 18},
    {'id': 2, 'name': 'Jerry', 'age': 19},
    {'id': 3, 'name': 'Alice', 'age': 20},
    {'id': 4, 'name': 'Bob', 'age': 21}
]

使用 JSON_TABLE 函数，可以将 JSON 格式的结果转换回表格形式。例如，以下查询语句将返回一个与 'students' 表格相同的结果：

SELECT t.*
FROM JSON_TABLE(
         '[{"id": 1, "name": "Tom", "age": 18},
           {"id": 2, "name": "Jerry", "age": 19},
           {"id": 3, "name": "Alice", "age": 20},
           {"id": 4, "name": "Bob", "age": 21}]',
         '$[*]'
         COLUMNS (
             id INT PATH '$.id',
             name VARCHAR(20) PATH '$.name',
             age INT PATH '$.age'
         )
     ) t;