在Schema-Less场景下使用MySQL：MySQL JSON Path的使用

随着 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 数据。更多参考：

The JSON Data Type @ MySQL Reference Manual

orczhou.com