如何在MySQL中从键值对行数据创建JSON_OBJECT

错误原因 #

你最后执行的SQL语句末尾多了一个多余的右括号)，直接导致语法解析失败。另外，即便修正这个语法错误，手动拼接字符串生成JSON_OBJECT的思路也存在隐患：

• 当MyValue包含单引号时，会引发JSON格式错误
• 数值类型字段（比如Population）会被强制转换为字符串，丢失原有数据类型

解决建议 #

方法1：使用JSON_OBJECTAGG（推荐） #

MySQL 5.7.22及以上版本提供了JSON_OBJECTAGG聚合函数，可直接将键值对行数据转换为JSON对象，无需手动拼接：

SELECT JSON_OBJECTAGG(MyKey, MyValue) AS result_json
FROM TempValuePair;

执行后会生成标准JSON：

{"Capital": "Buenos Aires", "Country": "Argentina", "Population": "45810000"}

如果需要保留Population的数值类型，可通过类型转换处理：

SELECT JSON_OBJECTAGG(
    MyKey, 
    CASE WHEN MyKey = 'Population' THEN CAST(MyValue AS UNSIGNED) ELSE MyValue END
) AS result_json
FROM TempValuePair;

此时Population会以数值形式存在于JSON中：

{"Capital": "Buenos Aires", "Country": "Argentina", "Population": 45810000}

方法2：修正手动拼接的实现（不推荐） #

若必须使用GROUP_CONCAT拼接参数，需先修正语法错误，再通过预处理语句执行动态生成的JSON_OBJECT调用：

1. 修正拼接逻辑，用QUOTE()函数自动处理字符串转义，同时保留数值类型：

SELECT GROUP_CONCAT(
    CONCAT(
        QUOTE(MyKey), ', ', 
        CASE WHEN MyKey = 'Population' THEN MyValue ELSE QUOTE(MyValue) END
    ) ORDER BY MyKey
) AS json_params
FROM TempValuePair;

2. 通过预处理语句执行动态SQL：

SET @json_params = (
    SELECT GROUP_CONCAT(
        CONCAT(
            QUOTE(MyKey), ', ', 
            CASE WHEN MyKey = 'Population' THEN MyValue ELSE QUOTE(MyValue) END
        ) ORDER BY MyKey
    ) FROM TempValuePair
);
SET @sql = CONCAT('SELECT JSON_OBJECT(', @json_params, ') AS result_json');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

内容的提问来源于stack exchange，提问作者Chris