如何在MySQL中提取python list格式的JSON值？

问题场景 #

数据库某列存储着Python风格的列表数据，示例如下：

该格式不符合标准JSON规范，无法直接使用json_extract()函数，需要将列表中的每个JSON对象提取到单独的新列，预期结果：

解决方案 #

方案1：纯字符串分割处理 #

如果列表中的JSON对象内部不含逗号，可以用MySQL字符串函数直接分割提取：

SELECT
  TRIM(SUBSTRING_INDEX(TRIM(BOTH '[]' FROM `Column`), ',', 1)) AS First_column,
  TRIM(SUBSTRING_INDEX(TRIM(BOTH '[]' FROM `Column`), ',', -1)) AS Second_column
FROM your_table;

• TRIM(BOTH '[]' FROM Column)：移除字段值首尾的[和]
• SUBSTRING_INDEX(..., ',', 1)：截取第一个逗号前的内容，再用TRIM()去掉前后空格，得到第一个JSON对象
• SUBSTRING_INDEX(..., ',', -1)：截取最后一个逗号后的内容，再去除空格，得到第二个JSON对象

方案2：转换为标准JSON后提取 #

如果JSON对象内部可能包含逗号，上述方法会出错，建议先将格式转换为标准JSON，再用JSON函数提取：

SELECT
  JSON_EXTRACT(cleaned_json, '$[0]') AS First_column,
  JSON_EXTRACT(cleaned_json, '$[1]') AS Second_column
FROM (
  SELECT
    REPLACE(REPLACE(`Column`, "'", '"'), ' , ', ',') AS cleaned_json
  FROM your_table
) AS temp;

• 内层查询中：REPLACE(Column, "'", '"')将可能存在的单引号替换为双引号；REPLACE(..., ' , ', ',')把列表元素间的,替换为标准JSON的逗号分隔
• 外层用JSON_EXTRACT()按数组索引提取对应位置的JSON对象

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