MySQL 高级函数之 JSON_TABLE()：JSON 数据到关系表的桥梁
各位朋友，大家好！今天我们来深入探讨 MySQL 中一个非常强大的函数：JSON_TABLE()。在现代应用开发中，JSON 已经成为数据交换和存储的常用格式。MySQL 5.7.22 版本引入了 JSON_TABLE() 函数，极大地简化了将 JSON 数据转换为关系表的过程，使得我们可以像操作普通关系表一样操作 JSON 数据。
1. JSON_TABLE() 函数概述
JSON_TABLE() 函数接受一个 JSON 文档和一个 JSON 路径表达式作为输入，并根据指定的列定义将 JSON 数据转换为一个虚拟的关系表。这个虚拟表可以像其他表一样在 SELECT 语句中使用，进行查询、过滤、排序等操作。
其基本语法如下：
JSON_TABLE(
json_doc,
path COLUMNS (
column_name data_type PATH json_path [DEFAULT value]
[, column_name data_type PATH json_path [DEFAULT value] ...]
)
) AS aliasjson_doc: 包含 JSON 数据的表达式。可以是一个 JSON 字符串，一个 JSON 列，或者一个返回 JSON 数据的函数。path: JSON 数组的路径，用于指定要迭代的 JSON 数组。通常使用'$[*]'来遍历整个 JSON 数组。COLUMNS (...): 定义了虚拟表的列。column_name: 虚拟表的列名。data_type: 列的数据类型，如INT,VARCHAR,DATE等。PATH json_path: JSON 路径，用于指定从 JSON 文档中提取哪个值赋给该列。DEFAULT value(可选): 如果在 JSON 文档中找不到指定的路径，则使用该默认值。
alias: 虚拟表的别名，用于在SELECT语句中引用。
2. JSON_TABLE() 函数的应用场景
JSON_TABLE() 函数在以下场景中非常有用：
- 将存储在 JSON 列中的数据转换为关系表进行查询和分析。 例如，将用户行为日志存储在 JSON 列中，然后使用
JSON_TABLE()将其转换为关系表，以便进行用户行为分析。 - 从外部 API 获取 JSON 数据，并将其转换为关系表进行处理。 例如，从一个提供天气信息的 API 获取 JSON 数据，然后使用
JSON_TABLE()将其转换为关系表，以便进行天气数据分析。 - 简化复杂 JSON 数据的查询。 当 JSON 数据结构比较复杂时，使用
JSON_EXTRACT()函数进行查询可能会比较繁琐。使用JSON_TABLE()可以将 JSON 数据转换为关系表，从而可以使用更简洁的 SQL 语句进行查询。
3. 示例：将 JSON 数组转换为关系表
假设我们有一个包含用户信息的 JSON 数组，存储在一个名为 users 的表中，列名为 user_data。 JSON 数组的结构如下：
[
{
"id": 1,
"name": "Alice",
"age": 30,
"city": "New York"
},
{
"id": 2,
"name": "Bob",
"age": 25,
"city": "London"
},
{
"id": 3,
"name": "Charlie",
"age": 35,
"city": "Paris"
}
]我们可以使用 JSON_TABLE() 函数将这个 JSON 数组转换为一个虚拟的关系表，并查询年龄大于 28 岁的用户：
SELECT
jt.id,
jt.name,
jt.age,
jt.city
FROM
users,
JSON_TABLE(
users.user_data,
'$[*]'
COLUMNS (
id INT PATH '$.id',
name VARCHAR(255) PATH '$.name',
age INT PATH '$.age',
city VARCHAR(255) PATH '$.city'
)
) AS jt
WHERE
jt.age > 28;这条 SQL 语句的执行过程如下：
users.user_data获取users表中user_data列的 JSON 数据。'$[*]'指定遍历 JSON 数组中的每个元素。COLUMNS (...)定义了虚拟表的列：id,name,age,city，并指定了每个列从 JSON 文档中提取值的路径。AS jt为虚拟表指定别名为jt。WHERE jt.age > 28过滤年龄大于 28 岁的用户。
查询结果如下：
| id | name | age | city |
|---|---|---|---|
| 1 | Alice | 30 | New York |
| 3 | Charlie | 35 | Paris |
4. 使用 DEFAULT 子句处理缺失值
如果 JSON 数据中某些字段缺失，我们可以使用 DEFAULT 子句指定默认值。例如，如果 JSON 数据中缺少 city 字段，我们可以将其默认值设置为 Unknown：
SELECT
jt.id,
jt.name,
jt.age,
jt.city
FROM
users,
JSON_TABLE(
users.user_data,
'$[*]'
COLUMNS (
id INT PATH '$.id',
name VARCHAR(255) PATH '$.name',
age INT PATH '$.age',
city VARCHAR(255) PATH '$.city' DEFAULT 'Unknown'
)
) AS jt;如果 user_data 中某个 JSON 对象没有 city 字段，那么 jt.city 的值将为 Unknown。
5. 处理嵌套 JSON 对象
JSON_TABLE() 也可以处理嵌套的 JSON 对象。假设我们的 JSON 数据结构如下：
[
{
"id": 1,
"name": "Alice",
"age": 30,
"address": {
"street": "123 Main St",
"city": "New York",
"zip": "10001"
}
},
{
"id": 2,
"name": "Bob",
"age": 25,
"address": {
"street": "456 Elm St",
"city": "London",
"zip": "SW1A 0AA"
}
}
]我们可以使用以下 SQL 语句提取用户的地址信息：
SELECT
jt.id,
jt.name,
jt.age,
jt.street,
jt.city,
jt.zip
FROM
users,
JSON_TABLE(
users.user_data,
'$[*]'
COLUMNS (
id INT PATH '$.id',
name VARCHAR(255) PATH '$.name',
age INT PATH '$.age',
street VARCHAR(255) PATH '$.address.street',
city VARCHAR(255) PATH '$.address.city',
zip VARCHAR(255) PATH '$.address.zip'
)
) AS jt;在这个例子中，我们使用 $.address.street, $.address.city, $.address.zip 来提取嵌套 JSON 对象中的值。
6. 处理 JSON 数组中的数组
JSON_TABLE() 还可以处理 JSON 数组中的数组。假设我们的 JSON 数据结构如下：
[
{
"id": 1,
"name": "Alice",
"age": 30,
"hobbies": ["reading", "hiking", "coding"]
},
{
"id": 2,
"name": "Bob",
"age": 25,
"hobbies": ["swimming", "cycling"]
}
]要将 hobbies 数组展开成多行，我们需要使用两次 JSON_TABLE() 函数。 第一次展开最外层的 JSON 数组，第二次展开 hobbies 数组。
SELECT
u.id,
u.name,
u.age,
h.hobby
FROM
users,
JSON_TABLE(
users.user_data,
'$[*]'
COLUMNS (
id INT PATH '$.id',
name VARCHAR(255) PATH '$.name',
age INT PATH '$.age',
hobbies JSON PATH '$.hobbies'
)
) AS u
CROSS JOIN JSON_TABLE(
u.hobbies,
'$[*]'
COLUMNS (
hobby VARCHAR(255) PATH '$'
)
) AS h;在这个例子中，我们首先使用 JSON_TABLE() 函数将最外层的 JSON 数组转换为一个虚拟表 u，然后使用 CROSS JOIN 和另一个 JSON_TABLE() 函数将 hobbies 数组展开成多行，并将结果存储在虚拟表 h 中。 CROSS JOIN 会将 u 表的每一行与 h 表的每一行进行组合。 hobbies JSON PATH '$.hobbies' 这句将 hobbies 这个json array 赋值给 u 这个虚拟表的 hobbies 列。而内部的 JSON_TABLE 函数则从 u.hobbies 这个 JSON array 中提取数据。 '$' 表示取当前 JSON 数组的元素值。
查询结果如下：
| id | name | age | hobby |
|---|---|---|---|
| 1 | Alice | 30 | reading |
| 1 | Alice | 30 | hiking |
| 1 | Alice | 30 | coding |
| 2 | Bob | 25 | swimming |
| 2 | Bob | 25 | cycling |
7. 数据类型转换
JSON_TABLE() 函数允许在定义列时指定数据类型，MySQL 会尝试将 JSON 数据转换为指定的数据类型。 如果转换失败，MySQL 会返回错误或 NULL 值，具体取决于 SQL 模式。 因此，在定义列的数据类型时，需要确保与 JSON 数据的实际类型兼容。
例如，如果 JSON 数据中的 age 字段是一个字符串，我们可以将其转换为整数类型：
SELECT
jt.id,
jt.name,
jt.age
FROM
users,
JSON_TABLE(
users.user_data,
'$[*]'
COLUMNS (
id INT PATH '$.id',
name VARCHAR(255) PATH '$.name',
age INT PATH '$.age'
)
) AS jt;如果 user_data 中某个 JSON 对象的 age 字段是一个无法转换为整数的字符串，那么 jt.age 的值将为 NULL (取决于 SQL 模式)。
8. 性能考虑
JSON_TABLE() 函数的性能取决于 JSON 数据的结构和大小，以及查询的复杂程度。 对于大型 JSON 数据，将其转换为关系表可能会比较耗时。 为了提高性能，可以考虑以下几点：
- 只提取需要的列。 避免提取不必要的列，可以减少数据转换的开销。
- 使用索引。 如果经常需要根据 JSON 数据中的某些字段进行查询，可以考虑在这些字段上创建索引。 但是，需要注意的是，在 JSON 列上创建索引可能会影响写入性能。 从MySQL 8.0.17开始，支持在JSON列上创建虚拟列索引，可以提高查询效率。
- 避免在
WHERE子句中使用复杂的 JSON 路径表达式。 复杂的 JSON 路径表达式可能会导致查询性能下降。 - 考虑将 JSON 数据转换为关系表并存储。 如果需要频繁地查询 JSON 数据，可以考虑将其转换为关系表并存储，以提高查询性能。 这需要额外的存储空间，但可以显著提高查询速度。
9. 一个更复杂的例子
假设我们有一个存储订单信息的 JSON 数组，结构如下：
[
{
"order_id": 1001,
"customer_id": 101,
"order_date": "2023-10-26",
"items": [
{
"product_id": 201,
"product_name": "Laptop",
"quantity": 1,
"price": 1200.00
},
{
"product_id": 202,
"product_name": "Mouse",
"quantity": 1,
"price": 25.00
}
]
},
{
"order_id": 1002,
"customer_id": 102,
"order_date": "2023-10-27",
"items": [
{
"product_id": 203,
"product_name": "Keyboard",
"quantity": 1,
"price": 75.00
},
{
"product_id": 204,
"product_name": "Monitor",
"quantity": 2,
"price": 300.00
}
]
}
]我们可以使用以下 SQL 语句提取订单信息和订单项信息：
SELECT
o.order_id,
o.customer_id,
o.order_date,
i.product_id,
i.product_name,
i.quantity,
i.price
FROM
orders,
JSON_TABLE(
orders.order_data,
'$[*]'
COLUMNS (
order_id INT PATH '$.order_id',
customer_id INT PATH '$.customer_id',
order_date DATE PATH '$.order_date',
items JSON PATH '$.items'
)
) AS o
CROSS JOIN JSON_TABLE(
o.items,
'$[*]'
COLUMNS (
product_id INT PATH '$.product_id',
product_name VARCHAR(255) PATH '$.product_name',
quantity INT PATH '$.quantity',
price DECIMAL(10, 2) PATH '$.price'
)
) AS i;这个 SQL 语句首先使用 JSON_TABLE() 函数将订单信息转换为一个虚拟表 o，然后使用 CROSS JOIN 和另一个 JSON_TABLE() 函数将订单项信息展开成多行，并将结果存储在虚拟表 i 中。
查询结果如下：
| order_id | customer_id | order_date | product_id | product_name | quantity | price |
|---|---|---|---|---|---|---|
| 1001 | 101 | 2023-10-26 | 201 | Laptop | 1 | 1200.00 |
| 1001 | 101 | 2023-10-26 | 202 | Mouse | 1 | 25.00 |
| 1002 | 102 | 2023-10-27 | 203 | Keyboard | 1 | 75.00 |
| 1002 | 102 | 2023-10-27 | 204 | Monitor | 2 | 300.00 |
10. JSON_TABLE 与其他 JSON 函数的比较
JSON_TABLE 不是 MySQL 中唯一的 JSON 函数。 其他常用的 JSON 函数包括：
JSON_EXTRACT()(或简写->): 用于从 JSON 文档中提取指定路径的值。JSON_VALUE(): 类似于JSON_EXTRACT(), 但返回标量值，而不是 JSON 对象或数组。 在 MySQL 8.0.21 之后引入。JSON_MODIFY(): 用于修改 JSON 文档中的值。JSON_INSERT(): 用于向 JSON 文档中插入新的键值对。JSON_REPLACE(): 用于替换 JSON 文档中已有的键值对的值。JSON_REMOVE(): 用于从 JSON 文档中删除键值对。
JSON_EXTRACT() 和 JSON_VALUE() 主要用于查询 JSON 文档中的特定值，而 JSON_TABLE() 则用于将整个 JSON 文档或 JSON 数组转换为关系表。 JSON_TABLE() 提供了更灵活的方式来处理 JSON 数据，可以进行更复杂的查询和分析。
| 功能 | JSON_EXTRACT()/JSON_VALUE() |
JSON_TABLE() |
|---|---|---|
| 主要用途 | 提取 JSON 中的单个值 | 将 JSON 转换为关系表 |
| 返回值类型 | JSON 对象/数组/标量值 | 虚拟的关系表 |
| 适用场景 | 简单的数据提取 | 复杂的查询和分析，需要将 JSON 转换为关系表 |
| 灵活性 | 较低 | 较高 |
| 性能（通常） | 较高 | 较低（对于大型 JSON 数据） |
11. 使用 JSON_TABLE 的一些建议
- 理解数据结构：在使用
JSON_TABLE之前，务必彻底了解 JSON 数据的结构。这包括了解哪些字段存在，它们的数据类型，以及是否存在嵌套结构或数组。 - 正确的 JSON 路径：使用正确的 JSON 路径至关重要。错误的路径会导致提取不到数据或提取到错误的数据。可以使用 MySQL 的 JSON 函数（如
JSON_EXTRACT）来测试 JSON 路径是否正确。 - 数据类型匹配：确保在
COLUMNS子句中指定的数据类型与 JSON 数据中的实际类型匹配。如果不匹配，可能会导致数据转换错误或查询失败。 - 处理 NULL 值：考虑 JSON 数据中可能存在的 NULL 值或缺失字段。可以使用
DEFAULT子句来为缺失字段提供默认值，或者使用IFNULL函数来处理 NULL 值。 - 性能优化：对于大型 JSON 数据，
JSON_TABLE的性能可能是一个问题。可以考虑以下优化措施：- 只提取需要的列。
- 在 JSON 列上创建虚拟列索引（MySQL 8.0.17+）。
- 避免在
WHERE子句中使用复杂的 JSON 路径表达式。 - 如果需要频繁地查询 JSON 数据，可以考虑将其转换为关系表并存储。
- 避免 SQL 注入：如果 JSON 数据来自用户输入，请务必进行适当的验证和转义，以防止 SQL 注入攻击。
- 版本兼容性：
JSON_TABLE函数是在 MySQL 5.7.22 中引入的。确保使用的 MySQL 版本支持该函数。 - 错误处理：当 JSON 数据格式不正确或 JSON 路径不存在时，
JSON_TABLE可能会返回错误或 NULL 值。请确保在应用程序中进行适当的错误处理。
灵活运用 JSON_TABLE，数据分析更高效
JSON_TABLE() 函数为 MySQL 带来了处理 JSON 数据的强大能力。通过将 JSON 数据转换为关系表，我们可以使用熟悉的 SQL 语法进行查询、分析和处理。 掌握 JSON_TABLE() 函数，可以极大地提高处理 JSON 数据的效率，并简化复杂的数据操作。希望今天的讲解能帮助大家更好地理解和应用 JSON_TABLE() 函数。
未来，随着 JSON 数据在各个领域的广泛应用，JSON_TABLE() 函数的重要性将日益凸显。 深入理解其原理和应用，将使我们能够更好地应对各种数据处理挑战。