本文由 简悦 SimpRead 转码， 原文地址 blog.csdn.net
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
