mysql的jsonTable使用及说明

MySQL 的 JSON_TABLE() 函数用于将 JSON 数据转换为关系型表格格式。以下是详细说明和使用示例：

一、基本语法

JSON_TABLE(
    json_data,            -- JSON 数据或列
    path_expression       -- JSON 路径
    COLUMNS (
        column_name data_type PATH json_path [JSON_TABLE options]
        [, ...]
    )
) [AS] alias_name

二、主要参数说明

1. json_data

• 包含 JSON 数据的列或变量
• 如：json_column, '{"id":1}'

2. path_expression

• 指定要展开的 JSON 数组路径
• 如：'$'（根数组），'$.items'

3. COLUMNS 定义

支持以下几种类型的列：

COLUMNS (
    -- 基本类型
    id INT PATH '$.id',

    -- 嵌套对象展开
    NESTED PATH '$.items[*]' COLUMNS (
        item_id INT PATH '$.item_id',
        name VARCHAR(50) PATH '$.name'
    ),

    -- 处理空值（默认值为NULL）
    price DECIMAL(10,2) PATH '$.price' DEFAULT '0.00' ON EMPTY,

    -- 处理错误（如类型不匹配）
    quantity INT PATH '$.qty' DEFAULT 0 ON ERROR,

    -- 是否存在标志
    has_discount BOOLEAN EXISTS PATH '$.discount'
)

三、使用示例

示例1：基本使用

-- 创建测试数据
CREATE TABLE orders (
    id INT PRIMARY KEY,
    order_data JSON
);

INSERT INTO orders VALUES 
(1, '{"order_id": 1001, "items": [{"id": 1, "name": "Apple", "price": 5.0}, {"id": 2, "name": "Banana", "price": 3.0}]}'),
(2, '{"order_id": 1002, "items": [{"id": 3, "name": "Orange", "price": 4.0}]}');

-- 展开JSON数组
SELECT *
FROM orders,
JSON_TABLE(
    order_data,
    '$.items[*]'
    COLUMNS (
        item_id INT PATH '$.id',
        item_name VARCHAR(50) PATH '$.name',
        item_price DECIMAL(10,2) PATH '$.price'
    )
) AS items;

示例2：复杂嵌套结构

-- 复杂JSON结构
SET @json = '{
    "department": "IT",
    "employees": [
        {
            "id": 101,
            "name": "John",
            "skills": ["Java", "MySQL", "Python"],
            "projects": [
                {"name": "Project A", "status": "Completed"},
                {"name": "Project B", "status": "Ongoing"}
            ]
        },
        {
            "id": 102,
            "name": "Jane",
            "skills": ["JavaScript", "React"],
            "projects": [
                {"name": "Project C", "status": "Planning"}
            ]
        }
    ]
}';

-- 多级展开
SELECT dept, emp_id, emp_name, skill, project_name, status
FROM JSON_TABLE(
    @json,
    '$.employees[*]'
    COLUMNS (
        dept VARCHAR(20) PATH '$.department',
        emp_id INT PATH '$.id',
        emp_name VARCHAR(50) PATH '$.name',
        -- 展开技能数组
        NESTED PATH '$.skills[*]' COLUMNS (
            skill VARCHAR(50) PATH '$'
        ),
        -- 展开项目数组
        NESTED PATH '$.projects[*]' COLUMNS (
            project_name VARCHAR(50) PATH '$.name',
            status VARCHAR(20) PATH '$.status'
        )
    )
) AS emp_data;

示例3：条件过滤和默认值

-- 使用条件判断和默认值
SELECT *
FROM JSON_TABLE(
    '[{"id":1,"score":85},{"id":2,"score":null},{"id":3}]',
    '$[*]'
    COLUMNS (
        id INT PATH '$.id',
        score INT PATH '$.score' DEFAULT 0 ON EMPTY,
        grade VARCHAR(2) PATH '$.score' 
            DEFAULT 'F' ON EMPTY
            -- 使用CASE WHEN逻辑
            AS (CASE 
                WHEN CAST(`score` AS UNSIGNED) >= 90 THEN 'A'
                WHEN CAST(`score` AS UNSIGNED) >= 80 THEN 'B'
                ELSE 'C'
            END),
        has_score BOOLEAN EXISTS PATH '$.score'
    )
) AS t;

示例4：与其他函数结合

-- 与聚合函数结合
SELECT 
    order_id,
    COUNT(*) as item_count,
    SUM(item_price) as total_amount
FROM orders,
JSON_TABLE(
    order_data,
    '$.items[*]'
    COLUMNS (
        order_id INT PATH '$.order_id',
        item_price DECIMAL(10,2) PATH '$.price'
    )
) AS items
GROUP BY order_id;

-- 与WHERE条件结合
SELECT *
FROM orders,
JSON_TABLE(
    order_data,
    '$.items[*]'
    COLUMNS (
        order_id INT PATH '$.order_id',
        item_id INT PATH '$.id',
        item_name VARCHAR(50) PATH '$.name',
        item_price DECIMAL(10,2) PATH '$.price'
    )
) AS items
WHERE item_price > 4.0;

四、注意事项

MySQL版本要求

• MySQL 8.0+ 支持 JSON_TABLE()
• 早期版本不可用

性能考虑

• 大数据量时注意性能
• 考虑物化视图或存储转换后的数据

错误处理

-- ON EMPTY: 路径不存在或值为空
-- ON ERROR: 类型转换错误等
price DECIMAL PATH '$.price' 
  DEFAULT 0.0 ON EMPTY 
  DEFAULT -1 ON ERROR

NULL处理

• JSON中的 null 会转换为 SQL NULL
• 使用 DEFAULT 子句处理空值

路径表达式

• 支持标准的JSON路径语法
• $ 表示根
• [*] 表示所有数组元素
• $.key 访问对象属性

五、使用场景

ETL处理：将JSON日志转为结构化数据 API数据解析：处理来自API的JSON响应 报表生成：从JSON字段提取维度数据 数据迁移：从文档数据库迁移到关系数据库

六、替代方案比较

方法	优点	缺点
JSON_TABLE()	功能强大，支持复杂结构	MySQL 8.0+，性能消耗大
JSON_EXTRACT()	简单易用	只能提取单个值
存储过程解析	灵活控制	代码复杂，维护困难
应用层处理	业务逻辑分离	需要额外开发

JSON_TABLE() 是处理复杂JSON数据的强大工具，特别适合需要将JSON数组展开为多行的场景。