如何拆分MYSQL存储的JSON/JSON数组 提取accounts字段导出可用表格数据

方案1：直接在MySQL中拆分后导出（推荐） #

适用MySQL 5.7及以上版本，无需额外工具，操作步骤少：

• 假设存储数据的表名为user_data，分两种存储场景对应不同SQL：
  1. 整段JSON数组存在一行的字段json_col中：

  SELECT 
      j.id,
      j.identifier,
      j.license,
      j.firstname,
      j.lastname,
      IFNULL(JSON_UNQUOTE(JSON_EXTRACT(j.accounts, '$.money')), 0) AS money,
      IFNULL(JSON_UNQUOTE(JSON_EXTRACT(j.accounts, '$.bank')), 0) AS bank,
      IFNULL(JSON_UNQUOTE(JSON_EXTRACT(j.accounts, '$.black_money')), 0) AS black_money
  FROM 
      user_data,
      JSON_TABLE(
          user_data.json_col,
          '$[*]' COLUMNS (
              id INT PATH '$.id',
              identifier VARCHAR(255) PATH '$.identifier',
              license VARCHAR(255) PATH '$.license',
              firstname VARCHAR(255) PATH '$.firstname',
              lastname VARCHAR(255) PATH '$.lastname',
              accounts JSON PATH '$.accounts'
          )
      ) AS j;
  

  2. 每行存储单条JSON对象，accounts为单独字段：

  SELECT 
      id,
      identifier,
      license,
      firstname,
      lastname,
      -- 如果accounts字段是字符串格式的JSON，替换为CAST(accounts AS JSON)->>'$.money'
      IFNULL(accounts->>'$.money', 0) AS money,
      IFNULL(accounts->>'$.bank', 0) AS bank,
      IFNULL(accounts->>'$.black_money', 0) AS black_money
  FROM user_data;
  

• 执行SQL后将查询结果直接导出为CSV格式，即可直接导入Google Sheets进行排序、筛选等操作。

方案2：导出原始数据后用Python拆分 #

适用MySQL版本过低不支持JSON函数的场景：

• 第一步：从MySQL中将原始数据导出为JSON格式，保存为raw_data.json
• 第二步：运行以下Python脚本生成可直接导入的标准CSV：

import json
import csv

# 读取原始数据
with open("raw_data.json", "r", encoding="utf-8") as f:
    raw_data = json.load(f)

result = []
for item in raw_data:
    # 解析accounts字段的JSON字符串
    accounts = json.loads(item["accounts"])
    result.append({
        "id": item["id"],
        "identifier": item["identifier"],
        "license": item["license"],
        "firstname": item["firstname"],
        "lastname": item["lastname"],
        "money": accounts.get("money", 0),
        "bank": accounts.get("bank", 0),
        "black_money": accounts.get("black_money", 0)
    })

# 输出CSV
with open("processed_data.csv", "w", encoding="utf-8", newline="") as f:
    writer = csv.DictWriter(f, fieldnames=result[0].keys())
    writer.writeheader()
    writer.writerows(result)

• 生成的processed_data.csv可直接导入Google Sheets使用。

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