当前位置：首页 > 问答 > 正文

快速搞定数据库表转换，教你用ON把json轻松变成表格数据

畅苗
问答
2026-01-06 00:49:43
24

你是不是也遇到过这种头疼的情况？后端工程师扔给你一个文件，或者从某个API接口拉下来一串数据，打开一看，全是像下面这样的“乱码”：

{
  "company": "某科技有限公司",
  "employees": [
    {
      "id": 101,
      "name": "张三",
      "department": "研发部",
      "skills": ["Java", "Python", "SQL"]
    },
    {
      "id": 102,
      "name": "李四",
      "department": "市场部",
      "projects": [
        {"name": "夏季促销", "budget": 50000},
        {"name": "品牌升级", "budget": 100000}
      ]
    }
  ]
}

这玩意儿就是JSON,它对人眼阅读还算友好，结构清晰，但对很多数据分析工具（比如老牌的Excel，或者一些BI工具）可就有点难办了，它们最喜欢的是规规矩矩的表格，一行就是一条记录，一列就是一个属性，想把上面那一大坨嵌套的数据变成整齐的表格，难道要复制粘贴到天荒地老吗？

当然不用！今天就来教你一个在现代数据库（比如MySQL 8.0以上、PostgreSQL、SQL Server 2016以上等）里超级好用的“魔法”——ON语句，是配合JSON_TABLE这样的函数来使用，这个方法的精髓，就像“数据搬运工老王”在他的知乎专栏里打的比方：ON就像是把你手里的一个复杂包裹（JSON数据）放在桌子上，然后当着你的面，一层一层地拆开，把里面的东西分门别类地摆好，瞬间变成一张一目了然的表格。

咱们就拿上面那个员工数据的例子来开刀,假设这串JSON数据已经存在数据库的一个叫company_data的表里了，这个表有一个自增ID列和一个存JSON数据的列，名叫json_content。

快速搞定数据库表转换，教你用ON把json轻松变成表格数据

我们的目标是：把每个员工的信息拆出来，变成一行一行的记录。

在MySQL 8.0里，我们可以这么干：

SELECT
  emp.*
FROM
  company_data,
  JSON_TABLE(
    json_content,
    '$.employees[*]'  -- 告诉数据库：从JSON的根$开始，找到employees数组，遍历里面每一个元素[*]
    COLUMNS (
      employee_id INT PATH '$.id',
      employee_name VARCHAR(100) PATH '$.name',
      department VARCHAR(50) PATH '$.department',
      skills JSON PATH '$.skills'  -- 注意：skills还是个数组，我们先原样取出来
    )
  ) AS emp;  -- 给这个由JSON_TABLE生成的临时表起个名字叫emp

执行完这句SQL,你会得到一个类似这样的表格：

快速搞定数据库表转换，教你用ON把json轻松变成表格数据

employee_id	employee_name	department	skills
101	张三	研发部	["Java", "Python", "SQL"]
102	李四	市场部	NULL

看,是不是清晰多了！每个员工占一行，他们的基本信息都成了单独的列，这里你发现了没？李四的skills是空的，因为他原来的JSON数据里就没有skills这个字段，而是有一个projects，这正是JSON灵活的地方，但也是转换时需要注意的。

这里的关键点，就像“SQL学习笔记”博客里强调的： JSON_TABLE函数里的PATH参数，是你的一幅“寻宝地图”，代表JSON的根目录，然后用点来深入下一层，用[*]来表示“遍历整个数组”，后面的COLUMNS部分，就是你告诉数据库：“地图找到了，现在请把宝藏按照我说的规矩挖出来”，把id挖出来放到employee_id这个列里，类型是整数INT。

那如果我还想继续拆呢？把张三会的技能也拆成单独的行？没问题，我们可以再来一次“拆包”！这就是ON的威力，可以层层递进。

快速搞定数据库表转换，教你用ON把json轻松变成表格数据

SELECT
  emp.employee_id,
  emp.employee_name,
  skill_list.skill_name
FROM
  company_data,
  JSON_TABLE(
    json_content,
    '$.employees[*]'
    COLUMNS (
      employee_id INT PATH '$.id',
      employee_name VARCHAR(100) PATH '$.name',
      skills JSON PATH '$.skills'
    )
  ) AS emp,
  JSON_TABLE(
    emp.skills,  -- 这次是从第一次拆包得到的skills列（还是个JSON数组）继续拆
    '$[*]'       -- 遍历这个skills数组里的每一个元素
    COLUMNS (
      skill_name VARCHAR(50) PATH '$'  -- 数组里的元素本身就是字符串，直接用$代表它自己
    )
  ) AS skill_list;

这下,得到的结果就更细了：

employee_id	employee_name	skill_name
101	张三	Java
101	张三	Python
101	张三	SQL

看到了吗？原来张三的一条记录，因为他有三个技能，被“爆炸”成了三条记录，这就是将嵌套的JSON数据扁平化 的典型操作，对于李四的那个项目列表，也可以用一模一样的方法继续拆解。

总结一下快速搞定的几个核心步骤，这也是“数据搬运工老王”提到的实战心法：

看清结构：先把你的JSON数据看清楚，哪里是对象，哪里是数组[]，你想拆到哪一层。
从根出发：在JSON_TABLE的PATH里，总是从开始你的路径。
逐层拆解：先用一个JSON_TABLE拆开最外层，然后把里面还包含数组的列，作为下一个JSON_TABLE的输入，继续拆，这就是在FROM子句里用逗号连接多个JSON_TABLE的奥妙。
定义列映射：在COLUMNS里仔细定义好JSON里的字段和你目标表格列的对应关系及数据类型。

这个方法主要适用于支持这些高级JSON函数的数据库系统,如果你的环境不支持（比如用的MySQL是老版本），可能就需要写程序或者用其他ETL工具来预处理了，但只要你用的数据库够新，掌握ON和JSON_TABLE的搭配，绝对是处理JSON数据转换的一把利器，能让你从繁琐的手工操作中彻底解放出来。

最后要注意的是,如果JSON数据量非常大，这种实时解析可能会对数据库性能有一定影响，对于超大规模或频繁的转换任务，最好还是在数据入库前就处理好，或者使用专门的数仓工具，但对于日常的、临时的数据清洗和转换需求，这个方法无疑是又快又直接的。