MySQL 中 JSON 相关的三类常用操作

前言

因为 MySQL 不是分析型数据库，因此对 json 和数组类型的支持不是很好，所以许多东西的实现就很有奇技淫巧的味道。本文整理了 json 相关的一些操作，以备不时之需。

json 字段在 MySQL是最近十年才被支持，目前还有许多未完善的特性。josn 字段还有一个最大的问题就是它在某种意义上违反了第一范式，即字段不具备原子性。至于是否的确违反了第一反思，也要考虑到业务场景，如果这个字段在业务上总是整存整取，那就可以认为具备原子性。但是现在已经到了 4202 年，是否还要严格遵循 1971 年提出的数据库规范也是见仁见智。

常用操作

读写

JSON 字段可以直接按照字符串来整存整取，还可以通过 JSON Path局部读取：

示例代码

mysql > CREATE TABLE facts (sentence JSON);

mysql > INSERT INTO facts VALUES
       (JSON_OBJECT("mascot", "Our mascot is a dolphin named "Sakila"."));

mysql > INSERT INTO facts VALUES
        ('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');

mysql > SELECT JSON_EXTRACT(sentence, "$.mascot") AS json_str, JSON_UNQUOTE(JSON_EXTRACT(sentence, "$.mascot")) as str FROM facts;

mysql > SELEC sentence->"$.mascot" AS json_str ,sentence->>"$.mascot" as str FROM facts;

+----------------------------------------------+-----------------------------------------+
| json_str                                     | str                                     |
+----------------------------------------------+-----------------------------------------+
| "Our mascot is a dolphin named "Sakila"."  | Our mascot is a dolphin named "Sakila". |
+----------------------------------------------+-----------------------------------------+

上面两条 SELECT 语句是等效的。

JSON_EXTRACT函数，指定 json 字段和 json path，返回指定 path 的 json字符串

-> 操作，与上面的函数等效，不同的写法

->> 操作，等于JSON_UNQUOTE(JSON_EXTRACT(sentence, "$.mascot"))。本质上，上面两种方式返回的还是 json，因此对于字符串，会被双引号包起来，里面的值也就带转义字符了。而这种方式返回的是对应的值，如果是字符串，直接返回字符串的原始值。但是对于其他复杂类型，没有什么区别。

在使用 json path 的时候，有一个问题，如果 key 是数字或者数字开头的，会出现错误 json本身允许这种情况，但是 json path 在解析的时候会有歧义，可以用双引号包裹起来，比如 sentence->'$."0day"'。

构造 JSON 数据

MySQL 提供了三个函数，用来构造三种类型的 JSON

JSON_ARRAY([val[, val] ...]) 构造数组
JSON_OBJECT([key, val[, key, val] ...]) 构造key-value 的对象/字典
JSON_QUOTE(string) 构造字符，主要是转义

修改 JSON 值

MySQL 提供了以下函数，对 JSON 进行局部修改：

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...) path 指定了一个数组，在数组尾部追加一个值 val
JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...) path 指定了一个数组的下标，在该下标插入一个值 val
JSON_INSERT(json_doc, path, val[, path, val] ...) 针对 object插入一个值，如果该 path 存在则会忽略，而不是覆盖。
JSON_REPLACE(json_doc, path, val[, path, val] ...) 覆盖指定 path 的值，如果 path 不存在则忽略。
JSON_SET(json_doc, path, val[, path, val] ...) 设置指定 path 的值，如果 path 存在责覆盖，不存在则设置。
JSON_MERGE(json_doc, json_doc[, json_doc] ...) 合并多个 JSON。
JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...) 不同策略的合并方法，详见官方示例。
JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...) 不同策略的合并方法，详见官方示例。
JSON_REMOVE(json_doc, path[, path] ...) 删除path 指定的值
JSON_UNQUOTE(json_val) 还原对字符串的转义结果， JSON_QUOTE 的逆运算

数组作为表/Lateral view

MySQL 没有数组类型，但是 JSON 有数组。数组好比一个小型的表，因此在处理起来有许多特别之处。

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

JSON_TABLE函数的意思大概就是把 JSON 处理成表来使用。

expr 是获得 JSON 的表达式。
path 指定了 expr 里面一个数组的位置。
COLUMNS 定义了如何处理数组中的每个元素，提取哪些列，并设置别名。

下面是一个范例

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[*]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

+------+------+
| xval | yval |
+------+------+
| 2    | 8    |
| 3    | 7    |
| 4    | 6    |
+------+------+

columns 里面也有一个 path，这个 path 是针对每个元素的，如果是简单元素的数组，可以直接用$表示整个元素作为一个列。

上面的例子用了一个常量的 json 字符串，我们可以跟表字段结合起来用。如下:

+----+---------------+
| id | name          |
+----+---------------+
| 1  | ["a","b","c"] |  
| 2  | ["b"]         |
+----+---------------+

mysql> select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

+----+------+  
| id | name |
+----+------+  
| 1  | a    |
| 1  | b    |
| 1  | c    |
| 2  | b    |
+----+------+

这个例子中，name 是一个 json 字段，我们把每一行的 name 用 json_table 处理成表的形式，再跟主表 join。就会产生 Lateral view 的效果。

索引

虚拟字段和二级索引

有这样一个表

mysql > CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `properties` json NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
)

properties 是一个 json 字段，他的数据结构如下：

{
  "uuid": "e7af5df8-f477-4b9b-b074-ad72fe17f502",
  "request": {
    "email": "[email protected]",
    "firstName": "Little",
    "formType": "vehicle-inquiry",
    "lastName": "Bobby",
    "message": "Hello, can you tell me what the specs are for this vehicle?",
    "postcode": "75016",
    "townCity": "Dallas"
  }
}

假设我们需要针对里面的 email 字段查询，我们可以创建生成字段：

ALTER TABLE activity_log ADD COLUMN email VARCHAR(255)
  GENERATED ALWAYS as (properties->>"$.request.email");

在MySQL中，支持两种生成字段Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

我们可以基于生成字段做如下的查询

mysql> SELECT id, email FROM activity_log;
+----+-------------------------+
| id | email                   |
+----+-------------------------+
|  1 | [email protected] |
+----+-------------------------+

进一步，我们可以创建索引来为查询加速

ALTER TABLE activity_log ADD INDEX email (email) USING BTREE;

可以看到使用了索引

mysql> EXPLAIN SELECT * FROM activity_log WHERE email = '[email protected]';
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: activity_log
   partitions: NULL
         type: ref
possible_keys: email
          key: email
      key_len: 768
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

二级索引详情查看官方文档 https://dev.mysql.com/doc/refman/8.4/en/create-table-secondary-indexes.html

多值索引

对于 JSON 数组，我们还可以创建多值索引，查找数组中是否包含某个（多个）值，这是 MySQL8.0.17 之后支持的新特性。

现在有这样一个表：

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );
mysql> INSERT INTO customers VALUES
    (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
    (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
    (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
    (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
    (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');

这个例子中，custinfo是一个 JSON 字段，JSON 中的 zipcode 是一个整型数组。

MySQL 支持用下面三种方式对 JSON 数组做查询

mysql> SELECT * FROM customers
 WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
mysql> SELECT * FROM customers
 WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
mysql> SELECT * FROM customers
 WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

我们用 explain 分析这三个查询都没有用到索引。我们可以用下面的方法创建多值索引试试。

mysql> ALTER TABLE customers
 ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

我们再对其中一条做 explain 看看结果

mysql> EXPLAIN SELECT * FROM customers
  WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | zips          | zips | 9       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+

我们看到使用到了前面创建的 zips 索引。更多详情参考官方文档

但是多值索引的使用还是很局限，只能是 unsigned 之类的值(8.0.33实测)，更丰富的数组类型在后续版本有支持。详情参考 cast array 官方文档