MySQL JSON 路径深入理解：$、[*]、[#]和.key
大家好，今天我们深入探讨 MySQL JSON 数据类型中的路径表达式。JSON 路径是查询和操作 JSON 文档的关键，理解这些路径表达式对于有效地使用 MySQL JSON 功能至关重要。我们将重点讲解 $、[*]、[#] 和 .key 这四个核心元素，并通过实例演示它们的应用场景。
JSON 数据类型基础回顾
在深入 JSON 路径之前，我们先简单回顾一下 MySQL 中的 JSON 数据类型。JSON 数据类型允许我们在数据库中存储和查询 JSON 格式的数据。这意味着我们可以存储复杂的数据结构，如对象和数组，而无需将它们分解成多个关系型表。
创建包含 JSON 列的表：
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
details JSON
);插入 JSON 数据：
INSERT INTO products (product_name, details) VALUES
('Laptop', '{"brand": "Dell", "model": "XPS 13", "specs": {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}}'),
('Smartphone', '{"brand": "Samsung", "model": "Galaxy S23", "specs": {"screen": "6.1 inch", "camera": "50MP", "storage": "256GB"}}'),
('Tablet', '{"brand": "Apple", "model": "iPad Pro", "specs": {"screen": "12.9 inch", "processor": "M2", "storage": "512GB"}}'),
('Headphones', '{"brand": "Sony", "model": "WH-1000XM5", "features": ["Noise Cancelling", "Bluetooth", "Hi-Res Audio"]}');
INSERT INTO products (product_name, details) VALUES
('Book', '[{"title": "The Lord of the Rings", "author": "J.R.R. Tolkien"}, {"title": "Pride and Prejudice", "author": "Jane Austen"}]');现在我们有了包含 JSON 数据的 products 表，可以开始学习如何使用 JSON 路径来访问和操作这些数据。
$：根对象
$ 符号表示 JSON 文档的根对象。它是所有 JSON 路径的起点。
示例：检索整个 JSON 文档
SELECT details FROM products WHERE product_name = 'Laptop';这个查询会返回 Laptop 产品的整个 details JSON 文档。
现在，我们使用 JSON 路径来提取特定的信息。
SELECT JSON_EXTRACT(details, '$') FROM products WHERE product_name = 'Laptop';虽然这个查询的结果和上一个查询相同，但它更清楚地表明我们正在使用 JSON 路径来访问根对象。JSON_EXTRACT() 函数是 MySQL 中用于从 JSON 文档中提取数据的关键函数。 $ 通常和其他路径表达式结合使用，单独使用的意义不大。
.key：访问 JSON 对象中的键值
.key 用于访问 JSON 对象中的特定键的值。它允许我们深入到 JSON 对象的层次结构中。
示例：检索 brand 属性
SELECT JSON_EXTRACT(details, '$.brand') FROM products WHERE product_name = 'Laptop';这个查询会返回 Laptop 产品的 brand 属性的值，即 Dell。
示例：检索嵌套的 cpu 属性
SELECT JSON_EXTRACT(details, '$.specs.cpu') FROM products WHERE product_name = 'Laptop';这个查询会返回 Laptop 产品 specs 对象中的 cpu 属性的值，即 Intel i7。
.key 可以链式使用，以访问嵌套在多层 JSON 对象中的属性。
使用 ->> 运算符简化语法
MySQL 提供了一个更简洁的语法来提取 JSON 属性，即 ->> 运算符。它等价于 JSON_EXTRACT() 函数，但会自动将结果转换为字符串。
SELECT details->>'$.brand' FROM products WHERE product_name = 'Laptop';
SELECT details->>'$.specs.cpu' FROM products WHERE product_name = 'Laptop';这两个查询与使用 JSON_EXTRACT() 的查询返回相同的结果，但语法更简洁。
[*]：访问 JSON 数组中的所有元素
[*] 用于访问 JSON 数组中的所有元素。它允许我们迭代数组中的每个元素，并对它们执行操作。
示例：如果 details 列包含一个 JSON 数组，如何访问所有元素
假设 details 列包含一个如下所示的 JSON 数组：
[
{"title": "The Lord of the Rings", "author": "J.R.R. Tolkien"},
{"title": "Pride and Prejudice", "author": "Jane Austen"}
]我们可以使用 [*] 来访问数组中的所有元素。
SELECT JSON_EXTRACT(details, '$[*].title') FROM products WHERE product_name = 'Book';这个查询会返回一个包含所有书籍标题的 JSON 数组：["The Lord of the Rings", "Pride and Prejudice"]。
*示例：结合 .key 和 `[]`**
我们可以结合 .key 和 [*] 来访问数组中每个元素的特定属性。
SELECT details->>'$[*].author' FROM products WHERE product_name = 'Book';这个查询会返回一个包含所有书籍作者的 JSON 数组：["J.R.R. Tolkien", "Jane Austen"]。
使用 JSON_TABLE() 函数处理 JSON 数组
虽然 [*] 可以提取数组中的元素，但它返回的是一个 JSON 数组。如果我们需要将数组中的每个元素作为单独的行进行处理，可以使用 JSON_TABLE() 函数。
SELECT jt.title, jt.author
FROM products
JOIN JSON_TABLE(details, '$[*]' COLUMNS (
title VARCHAR(255) PATH '$.title',
author VARCHAR(255) PATH '$.author'
)) AS jt
WHERE product_name = 'Book';这个查询会将 JSON 数组展开成一个虚拟表 jt，其中每一行代表数组中的一个元素。然后，我们可以像查询普通表一样查询这个虚拟表。
| title | author |
|---|---|
| The Lord of the Rings | J.R.R. Tolkien |
| Pride and Prejudice | Jane Austen |
[#]：数组的长度
[#] 用于获取 JSON 数组的长度。它返回数组中元素的数量。
示例：获取数组的长度
SELECT JSON_LENGTH(details) FROM products WHERE product_name = 'Book';或者也可以这样写：
SELECT JSON_EXTRACT(details, '$[#]') FROM products WHERE product_name = 'Book';这两个查询都会返回 2，因为 details 列包含一个包含两个元素的 JSON 数组。
[#] 的注意事项
[#]只能用于 JSON 数组。如果用于 JSON 对象，它会返回NULL。JSON_LENGTH()函数是获取 JSON 数组长度的更通用的方法，因为它也可以用于计算 JSON 对象中键的数量。
综合示例：复杂的 JSON 查询
让我们看一个更复杂的示例，演示如何结合使用 $、.key、[*] 和 [#] 来执行更高级的 JSON 查询。
假设我们想要找到所有具有 "Noise Cancelling" 功能的耳机。details 列的示例如下：
{"brand": "Sony", "model": "WH-1000XM5", "features": ["Noise Cancelling", "Bluetooth", "Hi-Res Audio"]}我们可以使用以下查询来实现：
SELECT product_name
FROM products
WHERE JSON_CONTAINS(details->>'$.features', '"Noise Cancelling"');JSON_CONTAINS() 函数检查 JSON 数组中是否包含指定的字符串。 或者，我们可以使用 JSON_SEARCH() 函数：
SELECT product_name
FROM products
WHERE JSON_SEARCH(details, 'one', 'Noise Cancelling') IS NOT NULL;JSON_SEARCH() 函数在 JSON 文档中搜索指定的字符串。如果找到该字符串，则返回该字符串的路径；否则，返回 NULL。
总结：掌握 JSON 路径是关键
理解 $、.key、[*] 和 [#] 是掌握 MySQL JSON 数据类型的关键。这些路径表达式允许我们有效地访问和操作 JSON 文档中的数据。通过结合使用这些元素，我们可以执行复杂的 JSON 查询，并从 JSON 数据中提取有用的信息。
JSON 路径的灵活应用
掌握 JSON 路径后，就能灵活的从JSON数据中提取、过滤和更新数据，实现更复杂的数据处理需求。
持续实践才能熟练掌握
JSON 路径的学习需要不断实践，多尝试不同的查询和操作，才能真正理解其工作原理和应用场景。
更多高级技巧等你探索
MySQL JSON 数据类型还有很多高级特性等待我们去探索，例如 JSON 值的更新、JSON 值的合并等等，继续学习，精益求精。