如何读取MySQL中存储的JSON数组并转为指定列格式输出？

针对你存储在MySQL rates 列中的嵌套JSON数组，我们可以利用MySQL的JSON处理函数将其转换为你需要的两列格式。下面分两种MySQL版本给出具体方案：

方案一：MySQL 8.0及以上版本（推荐） #

MySQL 8.0引入了JSON_TABLE函数，它可以直接将JSON数组拆分为关系型表结构，处理起来非常简洁：

SELECT 
    JSON_VALUE(sub_array, '$[0].value') AS `Column 1`,
    JSON_VALUE(sub_array, '$[1].value') AS `Column 2`
FROM your_table,
     JSON_TABLE(
         rates,
         '$[*]' COLUMNS (sub_array JSON PATH '$')
     ) AS jt;

代码解释： #

• JSON_TABLE(rates, '$[*]' COLUMNS (sub_array JSON PATH '$'))：把rates列中的外层JSON数组（$[*]表示数组的所有元素）拆分成多行，每行对应一个子数组，存放在sub_array列中。
• JSON_VALUE(sub_array, '$[0].value')：从每个子数组中提取第一个对象的value值，作为Column 1；同理$[1].value提取第二个对象的value作为Column 2。JSON_VALUE会自动去除字符串的引号，比JSON_EXTRACT+JSON_UNQUOTE更简洁。

方案二：MySQL 5.7版本（兼容旧版本） #

MySQL 5.7没有JSON_TABLE，我们可以通过生成数字序列配合JSON_EXTRACT来实现：

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(rates, CONCAT('$[', n-1, '][0].value'))) AS `Column 1`,
    JSON_UNQUOTE(JSON_EXTRACT(rates, CONCAT('$[', n-1, '][1].value'))) AS `Column 2`
FROM your_table,
     -- 生成对应数组长度的数字序列（这里示例数组有7个元素，所以生成1-7）
     (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 
      UNION ALL SELECT 7) AS numbers
-- 过滤掉数组中不存在的索引（避免出现NULL行）
WHERE JSON_EXTRACT(rates, CONCAT('$[', n-1, ']')) IS NOT NULL;

代码解释： #

• 子查询(SELECT 1 AS n UNION ALL ...)生成一个包含1到7的数字序列，对应JSON数组的索引（因为数组索引从0开始，所以用n-1）。
• CONCAT('$[', n-1, '][0].value')拼接出JSON路径，定位到每个子数组的第一个对象的value；JSON_UNQUOTE用于去除返回结果的引号。
• WHERE子句确保只处理数组中实际存在的元素，避免生成多余的NULL行。

示例输出 #

执行上述SQL后，你会得到如下格式的结果：

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