使用说明
JSON_TABLE() 是一种强大的 SQL 函数，用于将 JSON 数据转换为关系表格式。它允许我们将嵌套的 JSON 数据展开为行和列，从而更方便地进行查询和分析。
语法
JSON_TABLE(
expr,
path COLUMNS (
column_name column_type PATH 'json_path' [DEFAULT default_value] [ERROR ON ERROR | NULL ON ERROR],
...
)
) AS alias
expr: 包含 JSON 数据的表达式。path: JSON 路径，用于指定要展开的 JSON 对象或数组。COLUMNS: 定义要生成的列及其类型和路径。column_name: 生成的列名。column_type: 生成列的数据类型。json_path: JSON 路径，用于指定列的数据来源。DEFAULT default_value: 可选，指定默认值。ERROR ON ERROR | NULL ON ERROR: 可选，指定在解析错误时的行为。
示例
假设我们有一个包含 JSON 数据的表 orders，其结构如下：
CREATE TABLE orders (
id INT,
order_data JSON
);
表中的数据如下：
INSERT INTO orders ( id, order_data )
VALUES
2 ( 1, '{"customer": {"name": "John Doe", "age": 30}, "items": [{"product": "Laptop", "price": 1200}, {"product": "Mouse", "price": 25}]}' ),
3 ( 2, '{"customer": {"name": "Jane Smith", "age": 25}, "items": [{"product": "Phone", "price": 800}, {"product": "Charger", "price": 20}]}' );
我们希望将 order_data 中的 items 数组展开为行，并提取每个商品的名称和价格。可以使用 JSON_TABLE() 函数实现：
SELECT
o.id,
jt.product,
jt.price
FROM
orders o,
JSON_TABLE(
o.order_data,
'$.items[*]' COLUMNS (
product VARCHAR(50) PATH '$.product',
price DECIMAL(10, 2) PATH '$.price'
)
) AS jt;
结果将是：
| id | product | price |
|---|---|---|
| 1 | Laptop | 1200 |
| 1 | Mouse | 25 |
| 2 | Phone | 800 |
| 2 | Charger | 20 |
详细说明
- 定义 JSON 路径: 在
JSON_TABLE()函数中，'$.items[*]'表示我们要展开order_data中的items数组。[*]表示数组中的每个元素。 - 定义列: 在
COLUMNS子句中，我们定义了两个列product和price，并指定了它们的 JSON 路径$.product和$.price。 - 联接表: 使用
FROM orders o, JSON_TABLE(...) AS jt将orders表与生成的 JSON 表联接。
错误处理
可以使用 ERROR ON ERROR 或 NULL ON ERROR 来指定在解析错误时的行为：
ERROR ON ERROR: 在解析错误时抛出错误。NULL ON ERROR: 在解析错误时返回NULL。
例如：
JSON_TABLE(
o.order_data,
'$.items[*]' COLUMNS (
product VARCHAR(50) PATH '$.product' ERROR ON ERROR,
price DECIMAL(10, 2) PATH '$.price' NULL ON ERROR
)
) AS jt;
默认值
可以使用 DEFAULT 子句为列指定默认值：
JSON_TABLE(
o.order_data,
'$.items[*]' COLUMNS (
product VARCHAR(50) PATH '$.product' DEFAULT 'Unknown',
price DECIMAL(10, 2) PATH '$.price' DEFAULT 0
)
) AS jt;
总结
JSON_TABLE() 函数是处理 JSON 数据的强大工具，允许我们将嵌套的 JSON 数据转换为关系表格式，从而更方便地进行查询和分析。通过定义 JSON 路径和列，我们可以灵活地提取所需的数据，并处理解析错误和默认值。
