mysql 数组转json

基础概念

MySQL本身并不直接支持数组类型，但可以使用JSON类型来存储JSON数据。将MySQL中的数组转换为JSON格式，通常是指将MySQL中的数据结构转换为JSON字符串。

类型

MySQL中的数组可以通过以下几种方式转换为JSON：

简单数组：将一维数组转换为JSON数组。
嵌套数组：将多维数组转换为嵌套的JSON数组。
关联数组：将键值对数组转换为JSON对象。

应用场景

数据交换：在不同的系统之间交换数据时，使用JSON格式可以简化数据处理。
API响应：Web API通常返回JSON格式的数据，便于前端处理。
存储复杂数据：MySQL的JSON类型可以存储复杂的嵌套数据结构。

示例代码

假设我们有一个包含学生信息的表 students，结构如下：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    scores JSON
);

插入一些示例数据：

INSERT INTO students (id, name, scores) VALUES
(1, 'Alice', '[{"subject": "Math", "score": 90}, {"subject": "Science", "score": 85}]'),
(2, 'Bob', '[{"subject": "Math", "score": 88}, {"subject": "Science", "score": 92}]');

查询并转换数据为JSON：

SELECT id, name, JSON_ARRAYAGG(JSON_OBJECT('subject', subject, 'score', score)) AS scores_json
FROM (
    SELECT id, name, JSON_EXTRACT(scores, CONCAT('$[', idx, ']')) AS subject_score
    FROM students, JSON_TABLE(scores, '$[*]' COLUMNS (idx FOR ORDINALITY, subject VARCHAR(50), score INT)) AS jt
) AS subquery
GROUP BY id, name;

参考链接

常见问题及解决方法

问题：MySQL中的数组转换为JSON时出现格式错误

原因：可能是由于数据格式不正确或使用了错误的函数。

解决方法：

检查数据格式：确保数组数据格式正确，没有非法字符。
使用正确的函数：使用 JSON_ARRAYAGG 和 JSON_OBJECT 等函数来构建JSON格式。

例如：

SELECT id, name, JSON_ARRAYAGG(JSON_OBJECT('subject', subject, 'score', score)) AS scores_json
FROM (
    SELECT id, name, JSON_EXTRACT(scores, CONCAT('$[', idx, ']')) AS subject_score
    FROM students, JSON_TABLE(scores, '$[*]' COLUMNS (idx FOR ORDINALITY, subject VARCHAR(50), score INT)) AS jt
) AS subquery
GROUP BY id, name;

通过以上方法，可以确保将MySQL中的数组正确转换为JSON格式。