MySQL 将json转为行，JSON_TABLE函数详解

JSON_TABLE 是 MySQL 8.0 引入的一个强大函数，它用于将 JSON 数据转换为关系表的格式。通过 JSON_TABLE 函数，可以将 JSON 文档解析为关系表的列和行，使得在 SQL 查询中处理 JSON 数据更加方便。下面是 JSON_TABLE 的详细解释和使用示例：

语法

JSON_TABLE(
    json_doc,
    path COLUMNS (
        column_name column_type PATH path_expression
        [DEFAULT default_value]
        [ERROR ON ERROR clause]
        [NULL ON ERROR clause]
        [ERROR ON EMPTY clause]
        [NULL ON EMPTY clause]
    ) [AS] alias
)

参数解释

• json_doc: 一个合法的 JSON 文档。
• path: 一个 JSON 路径表达式，指定 JSON 文档中需要处理的部分。
• COLUMNS: 定义生成的表的列。
  • column_name: 生成表的列名。
  • column_type: 列的数据类型。
  • PATH path_expression: JSON 路径表达式，指定如何从 JSON 文档中提取数据。
  • DEFAULT default_value: 当提取的数据为空或出现错误时使用的默认值。
  • ERROR ON ERROR clause: 当发生错误时触发错误。
  • NULL ON ERROR clause: 当发生错误时返回 NULL。
  • ERROR ON EMPTY clause: 当路径表达式结果为空时触发错误。
  • NULL ON EMPTY clause: 当路径表达式结果为空时返回 NULL。
  • alias: 生成表的别名。

    示例

    假设有一个包含 JSON 数据的表 orders，其结构如下：

    CREATE TABLE orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        order_data JSON
    );
    

    假设插入以下 JSON 数据：

    INSERT INTO orders (order_data) VALUES
    ('{
        "order_id": 1,
        "customer": {
            "name": "John Doe",
            "address": "123 Elm St."
        },
        "items": [
            {"product_id": 101, "quantity": 2, "price": 9.99},
            {"product_id": 102, "quantity": 1, "price": 19.99}
        ]
    }');
    

    使用 JSON_TABLE 将 JSON 数据转换为关系表格式：

    SELECT 
        order_id, 
        customer_name, 
        product_id, 
        quantity, 
        price
    FROM orders,
    JSON_TABLE(
        orders.order_data,
        "$" COLUMNS (
            order_id INT PATH "$.order_id",
            customer_name VARCHAR(50) PATH "$.customer.name",
            NESTED PATH "$.items[*]" COLUMNS (
                product_id INT PATH "$.product_id",
                quantity INT PATH "$.quantity",
                price DECIMAL(10, 2) PATH "$.price"
            )
        )
    ) AS jt;
    

    结果

    order_id | customer_name | product_id | quantity | price
    ---------|---------------|------------|----------|-------
    1        | John Doe      | 101        | 2        | 9.99
    1        | John Doe      | 102        | 1        | 19.99
    

    解释

    • JSON_TABLE 通过 $ 符号访问整个 JSON 文档。
    • 使用 COLUMNS 子句定义生成的表的列。
      • order_id 列从 JSON 文档的 order_id 字段提取。
      • customer_name 列从 customer.name 字段提取。
      • NESTED PATH 子句用于处理嵌套的 JSON 数组 items。
        • 每个 item 对象的 product_id、quantity 和 price 字段分别被提取到相应的列中。

          JSON_TABLE 函数使得在 SQL 查询中处理复杂的 JSON 数据变得直观和高效，能够将 JSON 数据与关系数据无缝集成。

          参考链接

          • MySQL 8.0 Documentation - JSON_TABLE Function
          • MySQL 8.0 Reference Manual
          • MySQL JSON Functions
          • MySQL JSON Data Type and Functions
          • Official MySQL Blog