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

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

引用

CSDN

1.

https://blog.csdn.net/kaka_buka/article/details/138997949

MySQL 8.0引入了一个强大的函数JSON_TABLE，它可以将JSON数据转换为关系表格式，使得在SQL查询中处理JSON数据更加方便。本文将详细介绍JSON_TABLE函数的语法、参数，并通过具体示例展示其使用方法。

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