mysql 数组转json

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

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;

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;