随着 MySQL 对 JSON 类型的原生支持，操作 JSON 数据已变得非常高效与强大。在过去数年的版本中，MySQL 也在不断地增强 JSON 处理相关的功能。在 JSON 处理中需要非常频繁的使用“JSON Path” 语法，而这部分又是略微复杂的部分，本文将系统的介绍如何在 MySQL 中使用 JSON Path，包括语法规则、各种通配符用法、递归匹配等高级技巧，并通过丰富示例帮助开发者快速掌握。
什么是 JSON Path？
JSON Path 是一种表示法，用来描述如何在 JSON 文档中定位数据。类似于文件系统路径，JSON Path 指引着从 JSON 根节点出发，逐步深入结构内部。在 MySQL 中，几乎所有的 JSON相关的函数都会使用到，包括：JSON_EXTRACT()、JSON_SET()、JSON_REPLACE()、JSON_REMOVE()、JSON_CONTAINS()等。
我们看到的场景的写法类似于：$.name、$.colors[0]、$.store**.price等。
基础语法说明
JSON Path的基础语法，遵循以下规则：
$：表示 JSON 文档的根节点。.：用于访问对象中的属性。["key"]：另一种访问对象属性的方式，适合处理特殊字符的 key。[index]：访问数组中的元素。*：通配符，匹配所有子元素。**：递归通配符，匹配所有嵌套层级的元素[start to end]：数组范围选择
JSON Path 基本示例
示例表与示例数据
创建带 JSON字段的表，并写入数据：
CREATE TABLE t1 (
id INT PRIMARY KEY AUTO_INCREMENT,
data JSON
);
INSERT INTO t1 (data) VALUES
('{
"name": "Alice",
"age": 25,
"email": "alice@example.com"
}');提取对象字段
这里使用基本的$.name引用根节点中属性为name的对象，示例如下：
mysql> SELECT JSON_EXTRACT(data, '$.name') FROM t1;
+------------------------------+
| JSON_EXTRACT(data, '$.name') |
+------------------------------+
| "Alice" |
+------------------------------+
1 row in set (0.00 sec)也可以使用如下等价的写法data->'$.name'：
mysql> SELECT data->'$.name' FROM t1;
+----------------+
| data->'$.name' |
+----------------+
| "Alice" |
+----------------+访问数组元素
初始化如下数据：
-- truncate table t1;
INSERT INTO t1 (data) VALUES
('{
"colors": ["red", "green", "blue"]
}');先访问colors属性，再查找该数组对象的第一个元素（注意：编号是0），故 JSON Path 为$.colors[0]，示例如下：
mysql> SELECT data->'$.colors[0]' AS first_color FROM t1;
+-------------+
| first_color |
+-------------+
| "red" |
+-------------+访问数组的范围
除了像上述展示的，可以使用数值访问数组外，还可以使用0 to 1这样的语法表示一个范围，并访问数组中的多个元素：
mysql> SELECT data->'$.colors[0 to 1]' AS first_color FROM t1;
+------------------+
| first_color |
+------------------+
| ["red", "green"] |
+------------------+
mysql> SELECT data->'$.colors[1 to 1]' AS first_color FROM t1;
+-------------+
| ["green"] |
+-------------+
mysql> SELECT data->'$.colors[1 to 2]' AS first_color FROM t1;
+-------------------+
| ["green", "blue"] |
+-------------------+使用通配符
准备示例数据
为了展示相关的示例，这里先给出一个更为复杂的示例数据：
CREATE TABLE t1 (
id INT PRIMARY KEY AUTO_INCREMENT,
data JSON
);
-- truncate table t1;
INSERT INTO t1 (data) VALUES
('{
"store": {
"book": [
{
"category": "fiction",
"title": "Harry Potter",
"price": 29.99
},
{
"category": "fiction",
"title": "Lord of the Rings",
"price": 49.99
}
],
"bicycle": {
"color": "red",
"price": 19.95
}
}
}');使用通配符查询所有book
则可以使用如下的搜索表达式：$.store.book[*]：
SELECT data->'$.store.book[*]' AS all_books
FROM t1;
mysql> SELECT data->'$.store.book[*]' AS all_books
-> FROM t1;
+--------------------------------------------------------------+
| all_books |
+--------------------------------------------------------------+
| [{"price": 29.99, "title"...}, {"price": 49.99, "title"...}] |
+--------------------------------------------------------------+这里比较容易错误的写成：$.store.book.*、$.store.book.[*]或$.store.book*。
使用通配符递归查询
列出所有书店中的书名
依旧使用上述的数据，这里可以使用递归的通配符（**）查询结构中所有title属性的取值，则'$.store**.title'：
SELECT data->'$.store**.title' AS all_books
FROM t1;
+---------------------------------------+
| all_books |
+---------------------------------------+
| ["Harry Potter", "Lord of the Rings"] |
+---------------------------------------+当然，也可以改成直接从“根”处开始递归查找，即$**.title
mysql> SELECT data->'$**.title' AS all_books FROM t1;
+---------------------------------------+
| all_books |
+---------------------------------------+
| ["Harry Potter", "Lord of the Rings"] |
+---------------------------------------+类似的，我们还可以取出所有的价格：
SELECT
data->'$.store**.price' AS book_prices
FROM t1;
+-----------------------+
| book_prices |
+-----------------------+
| [29.99, 49.99, 19.95] |
+-----------------------+小结
熟悉 MySQL JSON Path Syntax 可以让开发者更加高效操作 JSON 数据。更多参考：
