MySQL JSON_TABLE函数详解：从JSON数据提取到表格转换的完整教程

MySQL的JSON_TABLE使用及说明

在MySQL 8.0版本中，引入了对JSON数据的强大支持，其中JSON_TABLE函数是最为重要和实用的一项特性。它可以将JSON数组或对象中的数据提取并转换成关系型表格的形式，从而让我们能够像操作普通表一样对JSON数据进行查询、过滤和连接（JOIN）操作。

无论是处理来自前端的结构化数据，还是解析从外部API（例如 www.ipipp.com）获取的JSON响应，JSON_TABLE都能极大简化数据处理的复杂度。

一、JSON_TABLE 基本语法

JSON_TABLE的语法结构如下：

JSON_TABLE(
    json_doc,
    path COLUMNS (column_definition)
) [AS] alias

参数说明：

• json_doc：有效的JSON文档，可以是JSON类型的列、变量或JSON字面量。

• path：JSON路径表达式，指定要提取的JSON节点（通常指向一个数组）。

• COLUMNS：定义返回的虚拟表的列结构。

• alias：为生成的虚拟表指定别名，在多表联查时必不可少。

二、COLUMNS 子句详解

COLUMNS子句是JSON_TABLE的核心，它支持以下四种列定义方式：

1. column_name TYPE PATH 'json_path'：提取指定路径的标量值（字符串、数字、布尔值等）。

2. NESTED PATH：处理嵌套的JSON数组，生成多行数据。

3. FOR ORDINALITY：为生成的行添加行号计数器（类似于1, 2, 3...），类型必须为INT。

4. EXISTS PATH：判断指定的JSON路径是否存在，返回1（存在）或0（不存在）。

三、数据准备

为了演示，我们创建一个包含JSON数据的用户表，并插入一些测试数据：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    info JSON
);

INSERT INTO users (name, info) VALUES
('张三', '{"age": 28, "hobbies": ["阅读", "游泳"], "addresses": [{"city": "北京", "district": "朝阳区"}, {"city": "上海", "district": "浦东新区"}]}'),
('李四', '{"age": 35, "hobbies": ["编程"], "addresses": [{"city": "广州", "district": "天河区"}]}'),
('王五', '{"age": 22, "hobbies": [], "addresses": []}');

四、使用示例

1. 提取基础标量值与数组

提取用户的年龄以及第一个爱好（通过数组索引）：

SELECT 
    u.name, 
    jt.age, 
    jt.hobby
FROM users u,
JSON_TABLE(
    u.info,
    '$' COLUMNS (
        age INT PATH '$.age',
        hobby VARCHAR(20) PATH '$.hobbies[0]';
    )
) AS jt;

2. 展开一维数组

如果要把JSON数组里的每一个元素都展开成单独的行，可以使用[*]通配符：

SELECT 
    u.name, 
    jt.hobby
FROM users u,
JSON_TABLE(
    u.info,
    '$.hobbies[*]' COLUMNS (
        hobby VARCHAR(20) PATH '$'
    )
) AS jt;

3. 使用 NESTED PATH 处理嵌套数组

当JSON文档中存在多层嵌套数组时，NESTED PATH可以实现笛卡尔积式的展开：

SELECT 
    u.name, 
    jt.age, 
    jt.city, 
    jt.district
FROM users u,
JSON_TABLE(
    u.info,
    '$' COLUMNS (
        age INT PATH '$.age',
        NESTED PATH '$.addresses[*]' COLUMNS (
            city VARCHAR(50) PATH '$.city',
            district VARCHAR(50) PATH '$.district';
        )
    )
) AS jt;

4. FOR ORDINALITY 与 EXISTS PATH 的应用

使用FOR ORDINALITY为嵌套的地址编排序号，并使用EXISTS PATH检查是否有hobbies节点：

SELECT 
    u.name, 
    jt.addr_id,
    jt.city,
    jt.has_hobbies
FROM users u,
JSON_TABLE(
    u.info,
    '$' COLUMNS (
        has_hobbies INT EXISTS PATH '$.hobbies',
        NESTED PATH '$.addresses[*]' COLUMNS (
            addr_id FOR ORDINALITY,
            city VARCHAR(50) PATH '$.city'
        )
    )
) AS jt;

五、注意事项与最佳实践

• 版本要求：JSON_TABLE仅在MySQL 8.0及以上版本可用，MySQL 5.7无法使用。

• 空值处理：如果JSON路径找不到对应的值，JSON_TABLE默认返回NULL。如果找不到匹配的行（例如空数组），则不会生成该行的数据。

• 性能问题：对大型JSON文档使用JSON_TABLE可能会带来性能开销。如果在高频查询中使用，建议配合虚拟列（Generated Columns）将常用的JSON字段提取为普通列并建立索引。

• 语法限制：JSON_TABLE只能用在FROM子句中，且必须为结果集指定别名。

通过熟练使用JSON_TABLE，开发者可以在关系型数据库和NoSQL数据结构之间搭建一座桥梁，在保留数据库事务和约束优势的同时，灵活应对多变的业务数据结构。

MySQLJSON_TABLEJSON数据解析数据提取NESTED PATH