mysql - 如何将 SQL 查询转换为 JSON 格式

Question

我正在尝试将 SQL 结果转换为 JSON 格式，但它显示错误。

SQL 查询:

 SELECT 
    User.name, 
    Course.title, 
    Member.role 
FROM User 
JOIN Member JOIN Course 
    ON User.user_id = Member.user_id 
    AND Member.course_id = Course.course_id 
ORDER BY 
    Course.title, 
    Member.role DESC, 
    User.name;

我也尝试过 FOR JSON PATH 和 FOR JSON AUTO 但出现以下错误:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for JSON AUTO' at line 1

Answer 1

您可以使用JSON_OBJECT()在MySQL中生成有效的json。

例如，这将为每一行生成一个 json 对象，其中键为 user_name、course_title 和 member_role，位于名为 js:

SELECT JSON_OBJECT(
    'user_name', User.name, 
    'course_title', Course.title, 
    'member_role', Member.role 
) as js
FROM User 
JOIN Member JOIN Course 
    ON User.user_id = Member.user_id 
    AND Member.course_id = Course.course_id 
ORDER BY 
    Course.title, 
    Member.role DESC, 
    User.name;

如果您想要一个由单行和单列组成的结果集，其中所有对象都聚合在一个数组中，您可以在 JSON_OBJECT() 之上使用 JSON_ARRAYAGG :

SELECT JSON_ARRAYAGG(js) js_array
FROM (
    SELECT JSON_OBJECT(
        'user_name', User.name, 
        'course_title', Course.title, 
        'member_role', Member.role 
    ) as js
    FROM User 
    JOIN Member JOIN Course 
        ON User.user_id = Member.user_id 
        AND Member.course_id = Course.course_id 
    ORDER BY 
        Course.title, 
        Member.role DESC, 
        User.name
);