MySQL高级函数之：`JSON`路径表达式：`$`、`[*]`、`[#]`和`.key`的深入理解。

MySQL JSON路径表达式：深入解析与应用

大家好，今天我们来深入探讨MySQL中JSON路径表达式的强大功能。JSON数据类型在现代数据库应用中扮演着越来越重要的角色，而MySQL提供的JSON路径表达式则是操作JSON数据的关键。我们将重点关注四个核心元素：$、[*]、[#]和.key，并结合实际案例，帮助大家理解和掌握它们的应用。

JSON数据类型基础

在深入JSON路径表达式之前，我们先快速回顾一下MySQL中的JSON数据类型。JSON数据类型允许我们在数据库中存储和操作JSON格式的数据，例如对象、数组、数值、字符串和布尔值。

例如，我们可以创建一个包含JSON列的表：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    details JSON
);

INSERT INTO employees (id, name, details) VALUES
(1, 'Alice', '{"age": 30, "city": "New York", "skills": ["Java", "Python", "SQL"]}'),
(2, 'Bob', '{"age": 25, "city": "London", "skills": ["JavaScript", "HTML", "CSS"]}'),
(3, 'Charlie', '{"age": 35, "city": "Paris", "skills": ["C++", "C#", "Java"]}');

现在，我们就可以使用JSON路径表达式来查询和修改details列中的数据。

$：根对象指示符

$符号是JSON路径表达式的根，它指向整个JSON文档。 换句话说，$代表的是整个JSON对象或数组。

例如，如果我们想检索employees表中所有员工的完整details信息，可以使用如下查询：

SELECT details FROM employees;

这个查询直接返回details列的完整JSON文档。 如果我们在JSON路径表达式中使用$，它代表的是整个JSON对象。 例如，'$.age'表示的是JSON对象中的age属性。

我们可以使用JSON_EXTRACT()函数来提取特定字段的值。例如，要提取所有员工的年龄，可以使用以下查询：

SELECT JSON_EXTRACT(details, '$.age') AS age FROM employees;

输出结果：

age
30
25
35

$是所有JSON路径表达式的基础，它为我们提供了访问JSON数据的入口点。

[*]：数组成员通配符

[*]用于选择JSON数组中的所有元素。它相当于一个循环，遍历数组中的每一个成员。

例如，假设我们想检索employees表中所有员工的所有技能。由于skills属性是一个JSON数组，我们可以使用[*]来访问每个技能。

SELECT JSON_EXTRACT(details, '$.skills[*]') AS skill FROM employees;

输出结果：

skill
"Java"
"Python"
"SQL"
"JavaScript"
"HTML"
"CSS"
"C++"
"C#"
"Java"

可以看到，查询返回了每个员工的所有技能，并将它们放在单独的行中。

[*]也可以与其他路径表达式组合使用。 例如，如果我们想要知道有多少员工拥有"Java"技能，可以这样查询：

SELECT COUNT(*)
FROM employees
WHERE JSON_CONTAINS(details, CAST('["Java"]' AS JSON), '$.skills');

这个查询首先使用JSON_CONTAINS()函数检查details.skills数组是否包含"Java"字符串。 CAST('["Java"]' AS JSON)将字符串转化为JSON数组，确保数据类型匹配。然后，WHERE子句过滤出包含"Java"技能的员工，最后COUNT(*)计算符合条件的员工数量。

更复杂的例子，假设我们的JSON结构稍作修改，将技能存储在更深层次的嵌套数组中：

UPDATE employees SET details = JSON_SET(details, '$.profile.technical_skills', JSON_ARRAY('Java', 'Python', 'SQL')) WHERE id = 1;
UPDATE employees SET details = JSON_SET(details, '$.profile.technical_skills', JSON_ARRAY('JavaScript', 'HTML', 'CSS')) WHERE id = 2;
UPDATE employees SET details = JSON_SET(details, '$.profile.technical_skills', JSON_ARRAY('C++', 'C#', 'Java')) WHERE id = 3;

现在details结构如下：

{
  "age": 30,
  "city": "New York",
  "skills": ["Java", "Python", "SQL"],
  "profile": {
    "technical_skills": ["Java", "Python", "SQL"]
  }
}

如果我们想获取所有员工的所有技术技能，可以使用如下查询：

SELECT JSON_EXTRACT(details, '$.profile.technical_skills[*]') AS skill FROM employees;

输出结果和之前类似，只是路径稍有变化。

[*]的强大之处在于它可以处理任意长度的数组，而不需要预先知道数组的长度。

[#]：数组长度

[#]用于获取JSON数组的长度。 它可以帮助我们了解数组中包含多少个元素。

例如，如果我们想知道每个员工有多少个技能，可以使用以下查询：

SELECT name, JSON_LENGTH(JSON_EXTRACT(details, '$.skills')) AS num_skills FROM employees;

输出结果：

name	num_skills
Alice	3
Bob	3
Charlie	3

JSON_LENGTH()函数接受一个JSON数组作为参数，并返回数组的长度。 在这个例子中，我们首先使用JSON_EXTRACT()提取details.skills数组，然后使用JSON_LENGTH()计算数组的长度。

[#]不能直接用在JSON路径表达式中，它需要配合JSON_LENGTH()函数使用。 例如，'$.skills[#]'是不合法的语法。

假设我们需要筛选出拥有超过2个技能的员工，可以使用以下查询：

SELECT name
FROM employees
WHERE JSON_LENGTH(JSON_EXTRACT(details, '$.skills')) > 2;

这个查询首先计算每个员工的技能数量，然后使用WHERE子句过滤出技能数量大于2的员工。

[#]在处理动态数组时非常有用，它可以帮助我们了解数组的大小，并根据数组的大小执行不同的操作。

.key：对象成员指示符

.key用于访问JSON对象中的特定属性，其中key是属性的名称。 它允许我们直接访问JSON对象中的特定字段。

例如，如果我们想检索employees表中所有员工的姓名和所在城市，可以使用以下查询：

SELECT name, JSON_EXTRACT(details, '$.city') AS city FROM employees;

输出结果：

name	city
Alice	"New York"
Bob	"London"
Charlie	"Paris"

在这个例子中，$.city指定了我们要提取details对象中的city属性。

.key可以与其他路径表达式组合使用，以访问嵌套对象中的属性。 例如，假设details对象包含一个address对象，address对象包含一个street属性，我们可以使用$.address.street来访问street属性。

UPDATE employees SET details = JSON_SET(details, '$.address', JSON_OBJECT('street', 'Main St', 'zip', '10001')) WHERE id = 1;
UPDATE employees SET details = JSON_SET(details, '$.address', JSON_OBJECT('street', 'Oxford St', 'zip', 'WC1B 4EA')) WHERE id = 2;
UPDATE employees SET details = JSON_SET(details, '$.address', JSON_OBJECT('street', 'Champs-Élysées', 'zip', '75008')) WHERE id = 3;

现在details结构如下：

{
  "age": 30,
  "city": "New York",
  "skills": ["Java", "Python", "SQL"],
  "address": {
    "street": "Main St",
    "zip": "10001"
  }
}

我们可以使用以下查询来检索所有员工的街道地址：

SELECT name, JSON_EXTRACT(details, '$.address.street') AS street FROM employees;

输出结果：

name	street
Alice	"Main St"
Bob	"Oxford St"
Charlie	"Champs-Élysées"

.key是访问JSON对象属性的最直接和最常用的方法。 它允许我们精确地定位到JSON数据中的特定字段。

组合应用：更复杂的查询

以上我们分别介绍了$、[*]、[#]和.key，现在我们将它们组合起来，构建更复杂的查询。

假设我们需要找到所有居住在"New York"并且拥有"Java"技能的员工。 我们可以使用以下查询：

SELECT name
FROM employees
WHERE JSON_EXTRACT(details, '$.city') = '"New York"'
  AND JSON_CONTAINS(details, CAST('["Java"]' AS JSON), '$.skills');

这个查询首先使用JSON_EXTRACT()提取city属性，并与"New York"进行比较。 然后，使用JSON_CONTAINS()检查skills数组是否包含"Java"字符串。 WHERE子句将两个条件组合起来，只返回同时满足这两个条件的员工。

再举一个例子，假设我们需要找到所有技能数量大于等于3的员工，并按照技能数量降序排列。 我们可以使用以下查询：

SELECT name, JSON_LENGTH(JSON_EXTRACT(details, '$.skills')) AS num_skills
FROM employees
WHERE JSON_LENGTH(JSON_EXTRACT(details, '$.skills')) >= 3
ORDER BY num_skills DESC;

这个查询首先使用JSON_LENGTH()计算每个员工的技能数量。 然后，使用WHERE子句过滤出技能数量大于等于3的员工。 最后，使用ORDER BY子句按照技能数量降序排列结果。

这些例子展示了如何将不同的JSON路径表达式组合起来，以实现更复杂的数据查询和分析。

JSON_TABLE：将JSON转换为关系表

MySQL 8.0引入了JSON_TABLE函数，它可以将JSON数据转换为关系表，从而可以使用更传统的SQL查询方式来操作JSON数据。

例如，我们可以使用JSON_TABLE将employees表中的skills数组转换为一个临时表，然后查询拥有"Java"技能的员工：

SELECT e.name
FROM employees e,
     JSON_TABLE(e.details -> '$.skills', '$[*]' COLUMNS (skill VARCHAR(255) PATH '$')) AS skills
WHERE skills.skill = 'Java';

在这个查询中，JSON_TABLE函数将e.details -> '$.skills' JSON数组转换为一个名为skills的临时表。 '$[*]'指定了数组中的每个元素都将成为表中的一行。 COLUMNS子句定义了表的列，skill VARCHAR(255) PATH '$'表示创建一个名为skill的VARCHAR列，并将每个数组元素的值赋给该列。 然后，我们可以像操作普通表一样，使用WHERE子句过滤出skill列值为"Java"的行。

JSON_TABLE的强大之处在于它可以将复杂的JSON结构转换为关系表，从而可以使用更灵活和强大的SQL查询来操作JSON数据。

性能考虑

在使用JSON路径表达式时，需要注意性能问题。 复杂的JSON路径表达式可能会导致查询效率降低。

以下是一些优化JSON查询的建议：

• 使用索引： 如果经常需要根据JSON数据进行查询，可以考虑在JSON列上创建索引。 MySQL支持在JSON列上创建虚拟列索引，这可以大大提高JSON查询的性能。
• 避免复杂的路径表达式： 尽量简化JSON路径表达式，避免过深的嵌套和复杂的通配符。
• 使用JSON_TABLE： 如果需要对JSON数据进行复杂的分析和处理，可以考虑使用JSON_TABLE将JSON数据转换为关系表，然后使用更传统的SQL查询方式。
• 数据结构优化： 优化JSON数据的结构，使其更易于查询和处理。 例如，避免过多的嵌套和冗余数据。

通过合理的优化，可以有效地提高JSON查询的性能，并充分发挥MySQL JSON数据类型的优势。

常见问题与注意事项

• 区分JSON_EXTRACT和->操作符: JSON_EXTRACT(json_doc, path)返回的是一个包含提取值的JSON文档，即使提取的是一个字符串或数字。而json_doc->path (或者 json_doc->>path 用于返回提取的字符串值) 返回的是提取值的字符串类型。选择哪个取决于你的需求和后续操作。
• 空值处理: 当JSON文档中路径不存在时，JSON_EXTRACT返回NULL。你需要使用COALESCE或其他函数来处理NULL值，避免出现意外错误。
• 类型转换: 从JSON文档中提取的数据通常是字符串类型。如果需要进行数值计算或其他类型操作，需要进行显式类型转换。
• 版本兼容性: 不同的MySQL版本可能对JSON功能的支持程度不同。 确保你的MySQL版本支持你使用的JSON函数和操作符。
• 安全性: 在使用JSON路径表达式时，需要注意安全性问题。 避免使用用户提供的路径表达式，以防止SQL注入攻击。

总结要点

• $代表整个JSON文档，是所有路径表达式的基础。
• [*]用于访问数组中的所有元素，相当于循环遍历。
• [#]配合JSON_LENGTH()函数获取数组长度。
• .key用于访问JSON对象中的特定属性，直接明了。

灵活运用JSON路径表达式

掌握了这些核心元素，我们就可以灵活地运用JSON路径表达式，在MySQL中高效地操作JSON数据，满足各种复杂的数据处理需求。