一文說透 MySQL JSON 數據類型（收藏）

• 2022 年 5 月 5 日
• 筆記
• MySQL

JSON 數據類型是 MySQL 5.7.8 開始支援的。在此之前，只能通過字元類型（CHAR，VARCHAR 或 TEXT ）來保存 JSON 文檔。

相對字元類型，原生的 JSON 類型具有以下優勢：

1. 在插入時能自動校驗文檔是否滿足 JSON 格式的要求。
2. 優化了存儲格式。無需讀取整個文檔就能快速訪問某個元素的值。

在 JSON 類型引入之前，如果我們想要獲取 JSON 文檔中的某個元素，必須首先讀取整個 JSON 文檔，然後在客戶端將其轉換為 JSON 對象，最後再通過對象獲取指定元素的值。

下面是 Python 中的獲取方式。

import json

# JSON 字元串:
x =  '{ "name":"John", "age":30, "city":"New York"}'

# 將 JSON 字元串轉換為 JSON 對象:
y = json.loads(x)

# 讀取 JSON 對象中指定元素的值:
print(y["age"])

這種方式有兩個弊端：一、消耗磁碟 IO，二、消耗網路頻寬，如果 JSON 文檔比較大，在高並發場景，有可能會打爆網卡。

如果使用的是 JSON 類型，相同的需求，直接使用 SQL 命令就可搞定。不僅能節省網路頻寬，結合後面提到的函數索引，還能降低磁碟 IO 消耗。

mysql> create table t(c1 json);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values('{ "name":"John", "age":30, "city":"New York"}');
Query OK, 1 row affected (0.01 sec)

mysql> select c1->"$.age" from t;
+-------------+
| c1->"$.age" |
+-------------+
| 30          |
+-------------+
1 row in set (0.00 sec)

本文將從以下幾個方面展開：

1. 什麼是 JSON。
2. JSON 欄位的增刪改查操作。
3. 如何對 JSON 欄位創建索引。
4. 如何將存儲 JSON 字元串的字元欄位升級為 JSON 欄位。
5. 使用 JSON 時的注意事項。
6. Partial Updates。
7. 其它 JSON 函數。

一、什麼是 JSON

JSON 是 JavaScript Object Notation（JavaScript 對象表示法）的縮寫，是一個輕量級的，基於文本的，跨語言的數據交換格式。易於閱讀和編寫。

JSON 的基本數據類型如下：

• 數值：十進位數，不能有前導 0，可以為負數或小數，還可以為 e 或 E 表示的指數。

• 字元串：字元串必須用雙引號括起來。

• 布爾值：true，false。

• 數組：一個由零或多個值組成的有序序列。每個值可以為任意類型。數組使用方括弧[] 括起來，元素之間用逗號,分隔。譬如，

  [1, "abc", null, true, "10:27:06.000000", {"id": 1}]
  

• 對象：一個由零或者多個鍵值對組成的無序集合。其中鍵必須是字元串，值可以為任意類型。

  對象使用花括弧{}括起來，鍵值對之間使用逗號,分隔，鍵與值之間用冒號:分隔。譬如，

  {"db": ["mysql", "oracle"], "id": 123, "info": {"age": 20}}
  

• 空值：null。

二、JSON 欄位的增刪改查操作

下面我們看看 JSON 欄位常見的增刪改查操作：

2.1 插入操作

可直接插入 JSON 格式的字元串。

mysql> create table t(c1 json);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values('[1, "abc", null, true, "08:45:06.000000"]');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values('{"id": 87, "name": "carrot"}');
Query OK, 1 row affected (0.01 sec)

也可使用函數，常用的有 JSON_ARRAY() 和 JSON_OBJECT()，前者用於構造 JSON 數組，後者用於構造 JSON 對象。如，

mysql> select json_array(1, "abc", null, true,curtime());
+--------------------------------------------+
| json_array(1, "abc", null, true,curtime()) |
+--------------------------------------------+
| [1, "abc", null, true, "10:12:25.000000"]  |
+--------------------------------------------+
1 row in set (0.01 sec)

mysql> select json_object('id', 87, 'name', 'carrot');
+-----------------------------------------+
| json_object('id', 87, 'name', 'carrot') |
+-----------------------------------------+
| {"id": 87, "name": "carrot"}            |
+-----------------------------------------+
1 row in set (0.00 sec)

對於 JSON 文檔，KEY 名不能重複。

如果插入的值中存在重複 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原則，會保留第一個 KEY，後面的將被丟棄掉。

從 MySQL 8.0.3 開始，遵循的是 last duplicate key wins 原則，只會保留最後一個 KEY。

下面通過一個具體的示例來看看兩者的區別。

MySQL 5.7.36

mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 10, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.02 sec)

MySQL 8.0.27

mysql> select json_object('key1',10,'key2',20,'key1',30);
+--------------------------------------------+
| json_object('key1',10,'key2',20,'key1',30) |
+--------------------------------------------+
| {"key1": 30, "key2": 20}                   |
+--------------------------------------------+
1 row in set (0.00 sec)

2.2 查詢操作

JSON_EXTRACT(json_doc, path[, path] …)

其中，json_doc 是 JSON 文檔，path 是路徑。該函數會從 JSON 文檔提取指定路徑（path）的元素。如果指定 path 不存在，會返回 NULL。可指定多個 path，匹配到的多個值會以數組形式返回。

下面我們結合一些具體的示例來看看 path 及 JSON_EXTRACT 的用法。

首先我們看看數組。

數組的路徑是通過下標來表示的。第一個元素的下標是 0。

mysql> select json_extract('[10, 20, [30, 40]]', '$[0]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]') |
+--------------------------------------------+
| 10                                         |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]','$[2][0]');
+--------------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0]', '$[1]','$[2][0]') |
+--------------------------------------------------------------+
| [10, 20, 30]                                                 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

除此之外，還可通過 [M to N] 獲取數組的子集。

mysql> select json_extract('[10, 20, [30, 40]]', '$[0 to 1]');
+-------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[0 to 1]') |
+-------------------------------------------------+
| [10, 20]                                        |
+-------------------------------------------------+
1 row in set (0.00 sec)

# 這裡的 last 代表最後一個元素的下標
mysql> select json_extract('[10, 20, [30, 40]]', '$[last-1 to last]');
+---------------------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[last-1 to last]') |
+---------------------------------------------------------+
| [20, [30, 40]]                                          |
+---------------------------------------------------------+
1 row in set (0.00 sec)

也可通過 [*] 獲取數組中的所有元素。

mysql> select json_extract('[10, 20, [30, 40]]', '$[*]');
+--------------------------------------------+
| json_extract('[10, 20, [30, 40]]', '$[*]') |
+--------------------------------------------+
| [10, 20, [30, 40]]                         |
+--------------------------------------------+
1 row in set (0.00 sec)

接下來，我們看看對象。

對象的路徑是通過 KEY 來表示的。

mysql> set @j='{"a": 1, "b": [2, 3], "a c": 4}';
Query OK, 0 rows affected (0.00 sec)

# 如果 KEY 在路徑表達式中不合法（譬如存在空格），則在引用這個 KEY 時，需用雙引號括起來。
mysql> select json_extract(@j, '$.a'), json_extract(@j, '$."a c"'), json_extract(@j, '$.b[1]');
+-------------------------+-----------------------------+----------------------------+
| json_extract(@j, '$.a') | json_extract(@j, '$."a c"') | json_extract(@j, '$.b[1]') |
+-------------------------+-----------------------------+----------------------------+
| 1                       | 4                           | 3                          |
+-------------------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)

除此之外，還可通過 .* 獲取對象中的所有元素。

mysql> select json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*');
+--------------------------------------------------------+
| json_extract('{"a": 1, "b": [2, 3], "a c": 4}', '$.*') |
+--------------------------------------------------------+
| [1, [2, 3], 4]                                         |
+--------------------------------------------------------+
1 row in set (0.00 sec)

# 這裡的 $**.b 匹配 $.a.b 和 $.c.b
mysql> select json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| json_extract('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

column->path

column->path，包括後面講到的 column->>path，都是語法糖，在實際使用的時候都會轉化為 JSON_EXTRACT。

column->path 等同於 JSON_EXTRACT(column, path) ，只能指定一個path。

create table t(c2 json);

insert into t values('{"empno": 1001, "ename": "jack"}'), ('{"empno": 1002, "ename": "mark"}');

mysql> select c2, c2->"$.ename" from t;
+----------------------------------+---------------+
| c2                               | c2->"$.ename" |
+----------------------------------+---------------+
| {"empno": 1001, "ename": "jack"} | "jack"        |
| {"empno": 1002, "ename": "mark"} | "mark"        |
+----------------------------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from t where c2->"$.empno" = 1001;
+------+----------------------------------+
| c1   | c2                               |
+------+----------------------------------+
|    1 | {"empno": 1001, "ename": "jack"} |
+------+----------------------------------+
1 row in set (0.00 sec)

column->>path

同 column->path 類似，只不過其返回的是字元串。以下三者是等價的。

• JSON_UNQUOTE( JSON_EXTRACT(column, path) )
• JSON_UNQUOTE(column -> path)
• column->>path

mysql> select c2->'$.ename',json_extract(c2, "$.ename"),json_unquote(c2->'$.ename'),c2->>'$.ename' from t;
+---------------+-----------------------------+-----------------------------+----------------+
| c2->'$.ename' | json_extract(c2, "$.ename") | json_unquote(c2->'$.ename') | c2->>'$.ename' |
+---------------+-----------------------------+-----------------------------+----------------+
| "jack"        | "jack"                      | jack                        | jack           |
| "mark"        | "mark"                      | mark                        | mark           |
+---------------+-----------------------------+-----------------------------+----------------+
2 rows in set (0.00 sec)

2.3 修改操作

JSON_INSERT(json_doc, path, val[, path, val] …)

插入新值。

僅當指定位置或指定 KEY 的值不存在時，才執行插入操作。另外，如果指定的 path 是數組下標，且 json_doc 不是數組，該函數首先會將 json_doc 轉化為數組，然後再插入新值。

下面我們看幾個示例。

mysql> select json_insert('1','$[0]',"10");
+------------------------------+
| json_insert('1','$[0]',"10") |
+------------------------------+
| 1                            |
+------------------------------+
1 row in set (0.00 sec)

mysql> select json_insert('1','$[1]',"10");
+------------------------------+
| json_insert('1','$[1]',"10") |
+------------------------------+
| [1, "10"]                    |
+------------------------------+
1 row in set (0.01 sec)

mysql> select json_insert('["1","2"]','$[2]',"10");
+--------------------------------------+
| json_insert('["1","2"]','$[2]',"10") |
+--------------------------------------+
| ["1", "2", "10"]                     |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_insert(@j, '$.a', 10, '$.c', '[true, false]');
+----------------------------------------------------+
| json_insert(@j, '$.a', 10, '$.c', '[true, false]') |
+----------------------------------------------------+
| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |
+----------------------------------------------------+
1 row in set (0.00 sec)

JSON_SET(json_doc, path, val[, path, val] …)

插入新值，並替換已經存在的值。

換言之，如果指定位置或指定 KEY 的值不存在，會執行插入操作，如果存在，則執行更新操作。

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_set(@j, '$.a', 10, '$.c', '[true, false]');
+-------------------------------------------------+
| json_set(@j, '$.a', 10, '$.c', '[true, false]') |
+-------------------------------------------------+
| {"a": 10, "b": [2, 3], "c": "[true, false]"}    |
+-------------------------------------------------+
1 row in set (0.00 sec)

JSON_REPLACE(json_doc, path, val[, path, val] …)

替換已經存在的值。

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_replace(@j, '$.a', 10, '$.c', '[true, false]');
+-----------------------------------------------------+
| json_replace(@j, '$.a', 10, '$.c', '[true, false]') |
+-----------------------------------------------------+
| {"a": 10, "b": [2, 3]}                              |
+-----------------------------------------------------+
1 row in set (0.00 sec)

2.4 刪除操作

JSON_REMOVE(json_doc, path[, path] …)

刪除 JSON 文檔指定位置的元素。

mysql> set @j = '{ "a": 1, "b": [2, 3]}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_remove(@j, '$.a');
+------------------------+
| JSON_REMOVE(@j, '$.a') |
+------------------------+
| {"b": [2, 3]}          |
+------------------------+
1 row in set (0.00 sec)

mysql> set @j = '["a", ["b", "c"], "d", "e"]';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_remove(@j, '$[1]');
+-------------------------+
| JSON_REMOVE(@j, '$[1]') |
+-------------------------+
| ["a", "d", "e"]         |
+-------------------------+
1 row in set (0.00 sec)

mysql> select json_remove(@j, '$[1]','$[2]');
+--------------------------------+
| JSON_REMOVE(@j, '$[1]','$[2]') |
+--------------------------------+
| ["a", "d"]                     |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select json_remove(@j, '$[1]','$[1]');
+--------------------------------+
| JSON_REMOVE(@j, '$[1]','$[1]') |
+--------------------------------+
| ["a", "e"]                     |
+--------------------------------+
1 row in set (0.00 sec)

最後一個查詢，雖然兩個 path 都是 ‘$[1]’ ，但作用對象不一樣，第一個 path 的作用對象是 ‘[“a”, [“b”, “c”], “d”, “e”]’ ，第二個 path 的作用對象是刪除了 ‘$[1]’ 後的數組，即 ‘[“a”, “d”, “e”]’ 。

三、如何對 JSON 欄位創建索引

同 TEXT，BLOB 欄位一樣，JSON 欄位不允許直接創建索引。

mysql> create table t(c1 json, index (c1));
ERROR 3152 (42000): JSON column 'c1' supports indexing only via generated columns on a specified JSON path.

即使支援，實際意義也不大，因為我們一般是基於文檔中的元素進行查詢，很少會基於整個  JSON 文檔。

對文檔中的元素進行查詢，就需要用到 MySQL 5.7 引入的虛擬列及函數索引。

下面我們來看一個具體的示例。

# C2 即虛擬列
# index (c2) 對虛擬列添加索引。
create table t ( c1 json, c2 varchar(10) as (JSON_UNQUOTE(c1 -> "$.name")), index (c2) );

insert into t (c1) values  ('{"id": 1, "name": "a"}'), ('{"id": 2, "name": "b"}'), ('{"id": 3, "name": "c"}'), ('{"id": 4, "name": "d"}');

mysql> explain select * from t where c2 = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref  | c2            | c2   | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t where c1->'$.name' = 'a';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref  | c2            | c2   | 43      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到，無論是使用虛擬列，還是文檔中的元素來查詢，都可以利用上索引。

注意，在創建虛擬列時需指定  JSON_UNQUOTE，將 c1 -> “$.name” 的返回值轉換為字元串。

四、如何將存儲 JSON 字元串的字元欄位升級為 JSON 欄位

在 MySQL 支援 JSON 類型之前，對於 JSON 文檔，一般是以字元串的形式存儲在字元類型（VARCHAR 或 TEXT）中。

在 JSON 類型出來之後，如何將這些字元欄位升級為 JSON 欄位呢？

為方便演示，這裡首先構建測試數據。

create table t (id int auto_increment primary key, c1 text);

insert into t (c1) values ('{"id": "1", "name": "a"}'), ('{"id": "2", "name": "b"}'), ('{"id": "3", "name": "c"}'), ('{"id", "name": "d"}');

注意，最後一個文檔有問題，不是合格的 JSON 文檔。

如果使用 DDL 直接修改欄位的數據類型，會報錯。

mysql> alter table t modify c1 json;
ERROR 3140 (22032): Invalid JSON text: "Missing a colon after a name of object member." at position 5 in value for column '#sql-7e1c_1f6.c1'.

下面，我們看看具體的升級步驟。

（1）使用 json_valid 函數找出不滿足 JSON 格式要求的文檔。

mysql> select * from t where json_valid(c1) = 0;
+----+---------------------+
| id | c1                  |
+----+---------------------+
|  4 | {"id", "name": "d"} |
+----+---------------------+
1 row in set (0.00 sec)

（2）處理不滿足 JSON 格式要求的文檔。

mysql> update t set c1='{"id": "4", "name": "d"}' where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

（3）將 TEXT 欄位修改為 JSON 欄位。

mysql> select * from t where json_valid(c1) = 0;
Empty set (0.00 sec)

mysql> alter table t modify c1 json;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

五、使用 JSON 時的注意事項

對於 JSON 類型，有以下幾點需要注意：

1. 在 MySQL 8.0.13 之前，不允許對 BLOB，TEXT，GEOMETRY，JSON 欄位設置默認值。從 MySQL 8.0.13 開始，取消了這個限制。

   設置時，注意默認值需通過小括弧()括起來，否則的話，還是會提示 JSON 欄位不允許設置默認值。

   mysql> create table t(c1 json not null default (''));
   Query OK, 0 rows affected (0.03 sec)
   
   mysql> create table t(c1 json not null default '');
   ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'c1' can't have a default value
   

2. 不允許直接創建索引，可創建函數索引。

3. JSON 列的最大大小和 LONGBLOB（LONGTEXT）一樣，都是 4G。

4. 插入時，單個文檔的大小受到 max_allowed_packet 的限制，該參數最大是 1G。

六、Partial Updates

在 MySQL 5.7 中，對 JSON 文檔進行更新，其處理策略是，刪除舊的文檔，再插入新的文檔。即使這個修改很微小，只涉及幾個位元組，也會替換掉整個文檔。很顯然，這種處理方式的效率較為低下。

在 MySQL 8.0 中，針對 JSON 文檔，引入了一項新的特性-Partial Updates（部分更新），支援 JSON 文檔的原地更新。得益於這個特性，JSON 文檔的處理性能得到了極大提升。

下面我們具體來看看。

6.1 使用 Partial Updates 的條件

為方便闡述，這裡先構造測試數據。

create table t (id int auto_increment primary key, c1 json);

insert into t (c1) values  ('{"id": 1, "name": "a"}'), ('{"id": 2, "name": "b"}'), ('{"id": 3, "name": "c"}'), ('{"id": 4, "name": "d"}');

mysql> select * from t;
+----+------------------------+
| id | c1                     |
+----+------------------------+
|  1 | {"id": 1, "name": "a"} |
|  2 | {"id": 2, "name": "b"} |
|  3 | {"id": 3, "name": "c"} |
|  4 | {"id": 4, "name": "d"} |
+----+------------------------+
4 rows in set (0.00 sec)

使用 Partial Updates 需滿足以下條件：

1. 被更新的列是 JSON 類型。

2. 使用 JSON_SET，JSON_REPLACE，JSON_REMOVE 進行 UPDATE 操作，如，

   update t set c1=json_remove(c1,'$.id') where id=1;
   

   不使用這三個函數，而顯式賦值，就不會進行部分更新，如，

   update t set c1='{"id": 1, "name": "a"}' where id=1;
   

3. 輸入列和目標列必須是同一列，如，

   update t set c1=json_replace(c1,'$.id',10) where id=1;
   

   否則的話，就不會進行部分更新，如，

   update t set c1=json_replace(c2,'$.id',10) where id=1;
   

4. 變更前後，JSON 文檔的空間使用不會增加。

關於最後一個條件，我們看看下面這個示例。

mysql> select *,json_storage_size(c1),json_storage_free(c1) from t where id=1;
+----+------------------------+-----------------------+-----------------------+
| id | c1                     | json_storage_size(c1) | json_storage_free(c1) |
+----+------------------------+-----------------------+-----------------------+
|  1 | {"id": 1, "name": "a"} |                    27 |                     0 |
+----+------------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

mysql> update t set c1=json_remove(c1,'$.id') where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *,json_storage_size(c1),json_storage_free(c1) from t where id=1;
+----+---------------+-----------------------+-----------------------+
| id | c1            | json_storage_size(c1) | json_storage_free(c1) |
+----+---------------+-----------------------+-----------------------+
|  1 | {"name": "a"} |                    27 |                     9 |
+----+---------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

mysql> update t set c1=json_set(c1,'$.id',3306) where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *,json_storage_size(c1),json_storage_free(c1) from t where id=1;
+----+---------------------------+-----------------------+-----------------------+
| id | c1                        | json_storage_size(c1) | json_storage_free(c1) |
+----+---------------------------+-----------------------+-----------------------+
|  1 | {"id": 3306, "name": "a"} |                    27 |                     0 |
+----+---------------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

mysql> update t set c1=json_set(c1,'$.id','mysql') where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *,json_storage_size(c1),json_storage_free(c1) from t where id=1;
+----+------------------------------+-----------------------+-----------------------+
| id | c1                           | json_storage_size(c1) | json_storage_free(c1) |
+----+------------------------------+-----------------------+-----------------------+
|  1 | {"id": "mysql", "name": "a"} |                    33 |                     0 |
+----+------------------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

示例中，用到了兩個函數：JSON_STORAGE_SIZE 和 JSON_STORAGE_FREE ，前者用來獲取 JSON 文檔的空間使用情況，後者用來獲取 JSON 文檔在執行原地更新後的空間釋放情況。

這裡一共執行了三次 UPDATE 操作，前兩次是原地更新，第三次不是。同樣是 JSON_SET 操作，為什麼第一次是原地更新，而第二次不是呢？

因為第一次的 JSON_SET 復用了 JSON_REMOVE 釋放的空間。而第二次的 JSON_SET 執行的是更新操作，且 ‘mysql’ 比 3306 需要更多的存儲空間。

6.2 如何在 binlog 中開啟 Partial Updates

Partial Updates 不僅僅適用於存儲引擎層，還可用於主從複製場景。

主從複製開啟 Partial Updates，只需將參數 binlog_row_value_options（默認為空）設置為 PARTIAL_JSON。

下面具體來看看，同一個 UPDATE 操作，開啟和不開啟 Partial Updates，在 binlog 中的記錄有何區別。

update t set c1=json_replace(c1,'$.id',10) where id=1;

不開啟

### UPDATE `slowtech`.`t`
### WHERE
###   @1=1
###   @2='{"id": "1", "name": "a"}'
### SET
###   @1=1
###   @2='{"id": 10, "name": "a"}'

開啟

### UPDATE `slowtech`.`t`
### WHERE
###   @1=1
###   @2='{"id": 1, "name": "a"}'
### SET
###   @1=1
###   @2=JSON_REPLACE(@2, '$.id', 10)

對比 binlog 的內容，可以看到，不開啟，無論是修改前的鏡像（before_image）還是修改後的鏡像（after_image），記錄的都是完整文檔。而開啟後，對於修改後的鏡像，記錄的是命令，而不是完整文檔，這樣可節省近一半的空間。

在將 binlog_row_value_options 設置為 PARTIAL_JSON 後，對於可使用 Partial Updates 的操作，在 binlog 中，不再通過 ROWS_EVENT 來記錄，而是新增了一個 PARTIAL_UPDATE_ROWS_EVENT 的事件類型。

需要注意的是，binlog 中使用 Partial Updates，只需滿足存儲引擎層使用 Partial Updates 的前三個條件，無需考慮變更前後，JSON 文檔的空間使用是否會增加。

6.3 關於 Partial Updates 的性能測試

首先構造測試數據，t 表一共有 16 個文檔，每個文檔近 10 MB。

create table t(id int auto_increment primary key,
               json_col json,
               name varchar(100) as (json_col->>'$.name'),
               age int as (json_col->'$.age'));

insert into t(json_col) values
(json_object('name', 'Joe', 'age', 24,
             'data', repeat('x', 10 * 1000 * 1000))),
(json_object('name', 'Sue', 'age', 32,
             'data', repeat('y', 10 * 1000 * 1000))),
(json_object('name', 'Pete', 'age', 40,
             'data', repeat('z', 10 * 1000 * 1000))),
(json_object('name', 'Jenny', 'age', 27,
             'data', repeat('w', 10 * 1000 * 1000)));

insert into t(json_col) select json_col from t;
insert into t(json_col) select json_col from t;

接下來，測試下述 SQL

update t set json_col = json_set(json_col, '$.age', age + 1);

在以下四種場景下的執行時間：

1. MySQL 5.7.36
2. MySQL 8.0.27
3. MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON
4. MySQL 8.0.27，binlog_row_value_options=PARTIAL_JSON + binlog_row_image=MINIMAL

分別執行 10 次，去掉最大值和最小值後求平均值。

最後的測試結果如下：

以 MySQL 5.7.36 的查詢時間作為基準：

1. MySQL 8.0 只開啟存儲引擎層的 Partial Updates，查詢時間比 MySQL 5.7 快 1.94 倍。
2. MySQL 8.0 同時開啟存儲引擎層和 binlog 中的 Partial Updates，查詢時間比 MySQL 5.7 快 4.87 倍。
3. 如果在 2 的基礎上，同時將 binlog_row_image 設置為 MINIMAL，查詢時間更是比 MySQL 5.7 快 102.22 倍。

當然，在生產環境，我們一般很少將 binlog_row_image 設置為 MINIMAL。

但即使如此，只開啟存儲引擎層和 binlog 中的 Partial Updates，查詢時間也比 MySQL 5.7 快 4.87 倍，性能提升還是比較明顯的。

七、其它 JSON 函數

7.1 查詢相關

JSON_CONTAINS(target, candidate[, path])

判斷 target 文檔是否包含 candidate 文檔，如果包含，則返回 1，否則是 0。

mysql> set @j = '{"a": [1, 2], "b": 3, "c": {"d": 4}}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_contains(@j, '1', '$.a'),json_contains(@j, '1', '$.b');
+-------------------------------+-------------------------------+
| json_contains(@j, '1', '$.a') | json_contains(@j, '1', '$.b') |
+-------------------------------+-------------------------------+
|                             1 |                             0 |
+-------------------------------+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_contains(@j,'{"d": 4}','$.a'),json_contains(@j,'{"d": 4}','$.c');
+------------------------------------+------------------------------------+
| json_contains(@j,'{"d": 4}','$.a') | json_contains(@j,'{"d": 4}','$.c') |
+------------------------------------+------------------------------------+
|                                  0 |                                  1 |
+------------------------------------+------------------------------------+
1 row in set (0.00 sec)

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)

判斷指定的 path 是否存在，存在，則返回 1，否則是 0。

函數中的 one_or_all 可指定 one 或 all，one 是任意一個路徑存在就返回 1，all 是所有路徑都存在才返回 1。

mysql> set @j = '{"a": [1, 2], "b": 3, "c": {"d": 4}}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_contains_path(@j, 'one', '$.a', '$.e'), json_contains_path(@j, 'all', '$.a', '$.e');
+---------------------------------------------+---------------------------------------------+
| json_contains_path(@j, 'one', '$.a', '$.e') | json_contains_path(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+---------------------------------------------+
|                                           1 |                                           0 |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_contains_path(@j, 'one', '$.c.d'),json_contains_path(@j, 'one', '$.a.d');
+----------------------------------------+----------------------------------------+
| json_contains_path(@j, 'one', '$.c.d') | json_contains_path(@j, 'one', '$.a.d') |
+----------------------------------------+----------------------------------------+
|                                      1 |                                      0 |
+----------------------------------------+----------------------------------------+
1 row in set (0.00 sec)

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] …])

返回某個字元串（search_str）在 JSON 文檔中的位置，其中，

• one_or_all：匹配的次數，one 是只匹配一次，all 是匹配所有。如果匹配到多個，結果會以數組的形式返回。
• search_str：子串，支援模糊匹配：% 和 _ 。
• escape_char：轉義符，如果該參數不填或為 NULL，則取默認轉義符\。
• path：查找路徑。

mysql> set @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_search(@j, 'one', 'abc'),json_search(@j, 'all', 'abc'),json_search(@j, 'all', 'ghi');
+-------------------------------+-------------------------------+-------------------------------+
| json_search(@j, 'one', 'abc') | json_search(@j, 'all', 'abc') | json_search(@j, 'all', 'ghi') |
+-------------------------------+-------------------------------+-------------------------------+
| "$[0]"                        | ["$[0]", "$[2].x"]            | NULL                          |
+-------------------------------+-------------------------------+-------------------------------+
1 row in set (0.00 sec)

mysql> select json_search(@j, 'all', '%b%', NULL, '$[1]'), json_search(@j, 'all', '%b%', NULL, '$[3]');
+---------------------------------------------+---------------------------------------------+
| json_search(@j, 'all', '%b%', NULL, '$[1]') | json_search(@j, 'all', '%b%', NULL, '$[3]') |
+---------------------------------------------+---------------------------------------------+
| NULL                                        | "$[3].y"                                    |
+---------------------------------------------+---------------------------------------------+
1 row in set (0.00 sec)

JSON_KEYS(json_doc[, path])

返回 JSON 文檔最外層的 key，如果指定了 path，則返回該 path 對應元素最外層的 key。

mysql> select json_keys('{"a": 1, "b": {"c": 30}}');
+---------------------------------------+
| json_keys('{"a": 1, "b": {"c": 30}}') |
+---------------------------------------+
| ["a", "b"]                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select json_keys('{"a": 1, "b": {"c": 30}}', '$.b');
+----------------------------------------------+
| json_keys('{"a": 1, "b": {"c": 30}}', '$.b') |
+----------------------------------------------+
| ["c"]                                        |
+----------------------------------------------+
1 row in set (0.00 sec)

JSON_VALUE(json_doc, path)

8.0.21 引入的，從 JSON 文檔提取指定路徑（path）的元素。

該函數的完整語法如下：

JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

on_empty:
    {NULL | ERROR | DEFAULT value} ON EMPTY

on_error:
    {NULL | ERROR | DEFAULT value} ON ERROR

其中：

• RETURNING type：返回值的類型，不指定，則默認是 VARCHAR(512)。不指定字符集，則默認是 utf8mb4，且區分大小寫。
• on_empty：如果指定路徑沒有值，會觸發 on_empty 子句， 默認是返回 NULL，也可指定 ERROR 拋出錯誤，或者通過 DEFAULT value 返回默認值。
• on_error：三種情況下會觸發 on_error 子句：從數組或對象中提取元素時，會解析到多個值；類型轉換錯誤，譬如將 “abc” 轉換為 unsigned 類型；值被 truncate 了。默認是返回 NULL。

mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.item');
+-------------------------------------------------------------+
| json_value('{"item": "shoes", "price": "49.95"}', '$.item') |
+-------------------------------------------------------------+
| shoes                                                       |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.price' returning decimal(4,2)) as price;
+-------+
| price |
+-------+
| 49.95 |
+-------+
1 row in set (0.00 sec)

mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.price1' error on empty);
ERROR 3966 (22035): No value was found by 'json_value' on the specified path.

mysql> select json_value('[1, 2, 3]', '$[1 to 2]' error on error);
ERROR 3967 (22034): More than one value was found by 'json_value' on the specified path.

mysql> select json_value('{"item": "shoes", "price": "49.95"}', '$.item' returning unsigned error on error) as price;
ERROR 1690 (22003): UNSIGNED value is out of range in 'json_value'

value MEMBER OF(json_array)

判斷 value 是否是 JSON 數組的一個元素，如果是，則返回 1，否則是 0。

mysql> select 17 member of('[23, "abc", 17, "ab", 10]');
+-------------------------------------------+
| 17 member of('[23, "abc", 17, "ab", 10]') |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast('[4,5]' as json) member of('[[3,4],[4,5]]');
+--------------------------------------------------+
| cast('[4,5]' as json) member of('[[3,4],[4,5]]') |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+
1 row in set (0.00 sec)

JSON_OVERLAPS(json_doc1, json_doc2)

MySQL 8.0.17 引入的，用來比較兩個 JSON 文檔是否有相同的鍵值對或數組元素，如果有，則返回 1，否則是 0。如果兩個參數都是標量，則判斷這兩個標量是否相等。

mysql> select json_overlaps('[1,3,5,7]', '[2,5,7]'),json_overlaps('[1,3,5,7]', '[2,6,8]');
+---------------------------------------+---------------------------------------+
| json_overlaps('[1,3,5,7]', '[2,5,7]') | json_overlaps('[1,3,5,7]', '[2,6,8]') |
+---------------------------------------+---------------------------------------+
|                                     1 |                                     0 |
+---------------------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> select json_overlaps('{"a":1,"b":2}', '{"c":3,"d":4,"b":2}');
+-------------------------------------------------------+
| json_overlaps('{"a":1,"b":2}', '{"c":3,"d":4,"b":2}') |
+-------------------------------------------------------+
|                                                     1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_overlaps('{"a":1,"b":2}', '{"c":3,"d":4,"b":10}');
+--------------------------------------------------------+
| json_overlaps('{"a":1,"b":2}', '{"c":3,"d":4,"b":10}') |
+--------------------------------------------------------+
|                                                      0 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_overlaps('5', '5'),json_overlaps('5', '6');
+-------------------------+-------------------------+
| json_overlaps('5', '5') | json_overlaps('5', '6') |
+-------------------------+-------------------------+
|                       1 |                       0 |
+-------------------------+-------------------------+
1 row in set (0.00 sec)

從 MySQL 8.0.17 開始，InnoDB 支援多值索引，可用在 JSON 數組中。當我們使用 JSON_CONTAINS、MEMBER OF、JSON_OVERLAPS 進行數組相關的操作時，可使用多值索引來加快查詢。

7.2 修改相關

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] …)

向數組指定位置追加元素。如果指定 path 不存在，則不添加。

mysql> set @j = '["a", ["b", "c"], "d"]';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_array_append(@j, '$[0]', 1, '$[1][0]', 2, '$[3]', 3);
+-----------------------------------------------------------+
| json_array_append(@j, '$[0]', 1, '$[1][0]', 2, '$[3]', 3) |
+-----------------------------------------------------------+
| [["a", 1], [["b", 2], "c"], "d"]                          |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set @j = '{"a": 1, "b": [2, 3], "c": 4}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_array_append(@j, '$.b', 'x', '$', 'z');
+---------------------------------------------+
| json_array_append(@j, '$.b', 'x', '$', 'z') |
+---------------------------------------------+
| [{"a": 1, "b": [2, 3, "x"], "c": 4}, "z"]   |
+---------------------------------------------+
1 row in set (0.00 sec)

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] …)

向數組指定位置插入元素。

mysql> set @j = '["a", ["b", "c"],{"d":"e"}]';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_array_insert(@j, '$[0]', 1);
+----------------------------------+
| json_array_insert(@j, '$[0]', 1) |
+----------------------------------+
| [1, "a", ["b", "c"], {"d": "e"}] |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select json_array_insert(@j, '$[1]', cast('[1,2]' as json));
+------------------------------------------------------+
| json_array_insert(@j, '$[1]', cast('[1,2]' as json)) |
+------------------------------------------------------+
| ["a", [1, 2], ["b", "c"], {"d": "e"}]                |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_array_insert(@j, '$[5]', 2);
+----------------------------------+
| json_array_insert(@j, '$[5]', 2) |
+----------------------------------+
| ["a", ["b", "c"], {"d": "e"}, 2] |
+----------------------------------+
1 row in set (0.00 sec)

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] …)

MySQL 8.0.3 引入的，用來合併多個 JSON 文檔。其合併規則如下：

1. 如果兩個文檔不全是 JSON 對象，則合併後的結果是第二個文檔。
2. 如果兩個文檔都是 JSON 對象，且不存在著同名 KEY，則合併後的文檔包括兩個文檔的所有元素，如果存在著同名 KEY，則第二個文檔的值會覆蓋第一個。

mysql> select json_merge_patch('[1, 2]', '[3, 4]'), json_merge_patch('[1, 2]', '{"a": 123}');
+--------------------------------------+------------------------------------------+
| json_merge_patch('[1, 2]', '[3, 4]') | json_merge_patch('[1, 2]', '{"a": 123}') |
+--------------------------------------+------------------------------------------+
| [3, 4]                               | {"a": 123}                               |
+--------------------------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_merge_patch('{"a": 1}', '{"b": 2}'),json_merge_patch('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
+------------------------------------------+-----------------------------------------------------------+
| json_merge_patch('{"a": 1}', '{"b": 2}') | json_merge_patch('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
+------------------------------------------+-----------------------------------------------------------+
| {"a": 1, "b": 2}                         | {"a": 3, "b": 2, "c": 4}                                  |
+------------------------------------------+-----------------------------------------------------------+
1 row in set (0.00 sec)

# 如果第二個文檔存在 null 值，文檔合併後不會輸出對應的 KEY。
mysql> select json_merge_patch('{"a":1, "b":2}', '{"a":3, "b":null}');
+---------------------------------------------------------+
| json_merge_patch('{"a":1, "b":2}', '{"a":3, "b":null}') |
+---------------------------------------------------------+
| {"a": 3}                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] …)

MySQL 8.0.3 引入的，用來代替 JSON_MERGE。也是用來合併文檔，但合併規則與 JSON_MERGE_PATCH 有所不同。

1. 兩個文檔中，只要有一個文檔是數組，則另外一個文檔會合併到該數組中。
2. 兩個文檔都是 JSON 對象，若存在著同名 KEY ，第二個文檔並不會覆蓋第一個，而是會將值 append 到第一個文檔中。

mysql> select json_merge_preserve('1','2'),json_merge_preserve('[1, 2]', '[3, 4]');
+------------------------------+-----------------------------------------+
| json_merge_preserve('1','2') | json_merge_preserve('[1, 2]', '[3, 4]') |
+------------------------------+-----------------------------------------+
| [1, 2]                       | [1, 2, 3, 4]                            |
+------------------------------+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select json_merge_preserve('[1, 2]', '{"a": 123}'), json_merge_preserve('{"a": 123}', '[3,4]');
+---------------------------------------------+--------------------------------------------+
| json_merge_preserve('[1, 2]', '{"a": 123}') | json_merge_preserve('{"a": 123}', '[3,4]') |
+---------------------------------------------+--------------------------------------------+
| [1, 2, {"a": 123}]                          | [{"a": 123}, 3, 4]                         |
+---------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_merge_preserve('{"a": 1}', '{"b": 2}'), json_merge_preserve('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }');
+---------------------------------------------+--------------------------------------------------------------+
| json_merge_preserve('{"a": 1}', '{"b": 2}') | json_merge_preserve('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }') |
+---------------------------------------------+--------------------------------------------------------------+
| {"a": 1, "b": 2}                            | {"a": [1, 3], "b": 2, "c": 4}                                |
+---------------------------------------------+--------------------------------------------------------------+
1 row in set (0.00 sec)

JSON_MERGE(json_doc, json_doc[, json_doc] …)

與 JSON_MERGE_PRESERVE 作用一樣，從 MySQL 8.0.3 開始不建議使用，後續會移除。

7.3 其它輔助函數

JSON_QUOTE(string)

生成有效的 JSON 字元串，主要是對一些特殊字元（如雙引號）進行轉義。

mysql> select json_quote('null'), json_quote('"null"'), json_quote('[1, 2, 3]');
+--------------------+----------------------+-------------------------+
| json_quote('null') | json_quote('"null"') | json_quote('[1, 2, 3]') |
+--------------------+----------------------+-------------------------+
| "null"             | "\"null\""           | "[1, 2, 3]"             |
+--------------------+----------------------+-------------------------+
1 row in set (0.00 sec)

除此之外，也可通過 CAST(value AS JSON) 進行類型轉換。

JSON_UNQUOTE(json_val)

將 JSON 轉義成字元串輸出。

mysql> select c2->'$.ename',json_unquote(c2->'$.ename'),
    -> json_valid(c2->'$.ename'),json_valid(json_unquote(c2->'$.ename')) from t;
+---------------+-----------------------------+---------------------------+-----------------------------------------+
| c2->'$.ename' | json_unquote(c2->'$.ename') | json_valid(c2->'$.ename') | json_valid(json_unquote(c2->'$.ename')) |
+---------------+-----------------------------+---------------------------+-----------------------------------------+
| "jack"        | jack                        |                         1 |                                       0 |
| "mark"        | mark                        |                         1 |                                       0 |
+---------------+-----------------------------+---------------------------+-----------------------------------------+
2 rows in set (0.00 sec)

直觀地看，沒加 JSON_UNQUOTE 字元串會用雙引號引起來，加了 JSON_UNQUOTE 就沒有。但本質上，前者是 JSON 中的 STRING 類型，後者是 MySQL 中的字元類型，這一點可通過 JSON_VALID 來判斷。

JSON_OBJECTAGG(key, value)

取表中的兩列作為參數，其中，第一列是 key，第二列是 value，返回 JSON 對象。如，

mysql> select * from emp;
+--------+----------+--------+
| deptno | ename    | sal    |
+--------+----------+--------+
|     10 | emp_1001 | 100.00 |
|     10 | emp_1002 | 200.00 |
|     20 | emp_1003 | 300.00 |
|     20 | emp_1004 | 400.00 |
+--------+----------+--------+
4 rows in set (0.00 sec)

mysql> select json_objectagg(ename,sal) from emp;
+----------------------------------------------------------------------------------+
| json_objectagg(ename,sal)                                                        |
+----------------------------------------------------------------------------------+
| {"emp_1001": 100.00, "emp_1002": 200.00, "emp_1003": 300.00, "emp_1004": 400.00} |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select deptno,json_objectagg(ename,sal) from emp group by deptno;
+--------+------------------------------------------+
| deptno | json_objectagg(ename,sal)                |
+--------+------------------------------------------+
|     10 | {"emp_1001": 100.00, "emp_1002": 200.00} |
|     20 | {"emp_1003": 300.00, "emp_1004": 400.00} |
+--------+------------------------------------------+
2 rows in set (0.00 sec)

JSON_ARRAYAGG(col_or_expr)

將列的值聚合成 JSON 數組，注意，JSON 數組中元素的順序是隨機的。

mysql> select json_arrayagg(ename) from emp;
+--------------------------------------------------+
| json_arrayagg(ename)                             |
+--------------------------------------------------+
| ["emp_1001", "emp_1002", "emp_1003", "emp_1004"] |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> select deptno,json_arrayagg(ename) from emp group by deptno;
+--------+--------------------------+
| deptno | json_arrayagg(ename)     |
+--------+--------------------------+
|     10 | ["emp_1001", "emp_1002"] |
|     20 | ["emp_1003", "emp_1004"] |
+--------+--------------------------+
2 rows in set (0.00 sec)

JSON_PRETTY(json_val)

將 JSON 格式化輸出。

mysql> select json_pretty("[1,3,5]");
+------------------------+
| json_pretty("[1,3,5]") |
+------------------------+
| [
  1,
  3,
  5
]      |
+------------------------+
1 row in set (0.00 sec)

mysql> select json_pretty('{"a":"10","b":"15","x":"25"}');
+---------------------------------------------+
| json_pretty('{"a":"10","b":"15","x":"25"}') |
+---------------------------------------------+
| {
  "a": "10",
  "b": "15",
  "x": "25"
}   |
+---------------------------------------------+
1 row in set (0.00 sec)

JSON_STORAGE_FREE(json_val)

MySQL 8.0 新增的，與 Partial Updates 有關，用於計算 JSON 文檔在進行部分更新後的剩餘空間。

JSON_STORAGE_SIZE(json_val)

MySQL 5.7.22 引入的，用於計算 JSON 文檔的空間使用情況。

JSON_DEPTH(json_doc)

返回 JSON 文檔的最大深度。對於空數組，空對象，標量值，其深度為 1。

mysql> select json_depth('{}'),json_depth('[10, 20]'),json_depth('[10, {"a": 20}]');
+------------------+------------------------+-------------------------------+
| json_depth('{}') | json_depth('[10, 20]') | json_depth('[10, {"a": 20}]') |
+------------------+------------------------+-------------------------------+
|                1 |                      2 |                             3 |
+------------------+------------------------+-------------------------------+
1 row in set (0.00 sec)

JSON_LENGTH(json_doc[, path])

返回 JSON 文檔的長度，其計算規則如下：

1. 如果是標量值，其長度為 1。
2. 如果是數組，其長度為數組元素的個數。
3. 如果是對象，其長度為對象元素的個數。
4. 不包括嵌套數據和嵌套對象的長度。

mysql> select json_length('"abc"');
+----------------------+
| json_length('"abc"') |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> select json_length('[1, 2, {"a": 3}]');
+---------------------------------+
| json_length('[1, 2, {"a": 3}]') |
+---------------------------------+
|                               3 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select json_length('{"a": 1, "b": {"c": 30}}');
+-----------------------------------------+
| json_length('{"a": 1, "b": {"c": 30}}') |
+-----------------------------------------+
|                                       2 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select json_length('{"a": 1, "b": {"c": 30}}', '$.a');
+------------------------------------------------+
| json_length('{"a": 1, "b": {"c": 30}}', '$.a') |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
1 row in set (0.00 sec)

JSON_TYPE(json_val)

返回 JSON 值的類型。

mysql> select json_type('123');
+------------------+
| json_type('123') |
+------------------+
| INTEGER          |
+------------------+
1 row in set (0.00 sec)

mysql> select json_type('"abc"');
+--------------------+
| json_type('"abc"') |
+--------------------+
| STRING             |
+--------------------+
1 row in set (0.00 sec)

mysql> select json_type(cast(now() as json));
+--------------------------------+
| json_type(cast(now() as json)) |
+--------------------------------+
| DATETIME                       |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select json_type(json_extract('{"a": [10, true]}', '$.a'));
+-----------------------------------------------------+
| json_type(json_extract('{"a": [10, true]}', '$.a')) |
+-----------------------------------------------------+
| ARRAY                                               |
+-----------------------------------------------------+
1 row in set (0.00 sec)

JSON_VALID(val)

判斷給定值是否是有效的 JSON 文檔。

mysql> select json_valid('hello'), json_valid('"hello"');
+---------------------+-----------------------+
| json_valid('hello') | json_valid('"hello"') |
+---------------------+-----------------------+
|                   0 |                     1 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

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

從 JSON 文檔中提取數據並以表格的形式返回。

該函數的完整語法如下：

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

column_list:
    column[, column][, ...]

column:
    name FOR ORDINALITY
    |  name type PATH string_path [on_empty] [on_error]
    |  name type EXISTS PATH string_path
    |  NESTED [PATH] path COLUMNS (column_list)

on_empty:
    {NULL | DEFAULT json_string | ERROR} ON EMPTY

on_error:
    {NULL | DEFAULT json_string | ERROR} ON ERROR

其中，

• expr：可以返回 JSON 文檔的表達式。可以是一個標量（ JSON 文檔 ），列名或者一個函數調用（ JSON_EXTRACT(t1.json_data,’$.post.comments’) ）。
• path：JSON 的路徑表達式，
• column：列的類型，支援以下四種類型：
• name FOR ORDINALITY：序號。name 是列名。
• name type PATH string_path [on_empty] [on_error]：提取指定路徑（ string_path ）的元素。name 是列名，type 是 MySQL 中的數據類型。
• name type EXISTS PATH string_path：指定路徑（ string_path ）的元素是否存在。
• NESTED [PATH] path COLUMNS (column_list)：將嵌套對象或數組與來自父對象或數組的 JSON 值扁平化為一行輸出。

select *
 from
   json_table(
     '[{"x":2, "y":"8", "z":9, "b":[1,2,3]}, {"x":"3", "y":"7"}, {"x":"4", "y":6, "z":10}]',
     "$[*]" columns(
       id for ordinality,
       xval varchar(100) path "$.x",
       yval varchar(100) path "$.y",
       z_exist int exists path "$.z",
       nested path '$.b[*]' columns (b INT PATH '$')
     )
   ) as t;
+------+------+------+---------+------+
| id   | xval | yval | z_exist | b    |
+------+------+------+---------+------+
|    1 | 2    | 8    |       1 |    1 |
|    1 | 2    | 8    |       1 |    2 |
|    1 | 2    | 8    |       1 |    3 |
|    2 | 3    | 7    |       0 | NULL |
|    3 | 4    | 6    |       1 | NULL |
+------+------+------+---------+------+
5 rows in set (0.00 sec)

JSON_SCHEMA_VALID(schema,document)

判斷 document （ JSON 文檔 ）是否滿足 schema （ JSON 對象）定義的規範要求。完整的規範要求可參考 Draft 4 of the JSON Schema specification 。如果不滿足，可通過 JSON_SCHEMA_VALIDATION_REPORT() 獲取具體的原因。

以下面這個 schema 為例。

set @schema = '{
   "type": "object",
   "properties": {
     "latitude": {
       "type": "number",
       "minimum": -90,
       "maximum": 90
     },
     "longitude": {
       "type": "number",
       "minimum": -180,
       "maximum": 180
     }
   },
   "required": ["latitude", "longitude"]
}';

它的要求如下：

1. document 必須是 JSON 對象。
2. JSON 對象必需的兩個屬性是 latitude 和 longitude。
3. latitude 和 longitude 必須是數值類型，且兩者的大小分別在 -90 ～ 90，-180 ～ 180 之間。

下面通過具體的 document 來測試一下。

mysql> set @document = '{"latitude": 63.444697,"longitude": 10.445118}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_schema_valid(@schema, @document);
+---------------------------------------+
| json_schema_valid(@schema, @document) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> set @document = '{"latitude": 63.444697}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_schema_valid(@schema, @document);
+---------------------------------------+
| json_schema_valid(@schema, @document) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select json_pretty(json_schema_validation_report(@schema, @document))\G
*************************** 1. row ***************************
json_pretty(json_schema_validation_report(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'",
  "schema-location": "#",
  "document-location": "#",
  "schema-failed-keyword": "required"
}
1 row in set (0.00 sec)

mysql> set @document = '{"latitude": 91,"longitude": 0}';
Query OK, 0 rows affected (0.00 sec)

mysql> select json_schema_valid(@schema, @document);
+---------------------------------------+
| json_schema_valid(@schema, @document) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select json_pretty(json_schema_validation_report(@schema, @document))\G
*************************** 1. row ***************************
json_pretty(json_schema_validation_report(@schema, @document)): {
  "valid": false,
  "reason": "The JSON document location '#/latitude' failed requirement 'maximum' at JSON Schema location '#/properties/latitude'",
  "schema-location": "#/properties/latitude",
  "document-location": "#/latitude",
  "schema-failed-keyword": "maximum"
}
1 row in set (0.00 sec)

八、總結

如果要使用 JSON 類型，推薦使用 MySQL 8.0。相比於 MySQL 5.7，Partial update 帶來的性能提升還是十分明顯的。

Partial update 在存儲引擎層是默認開啟的，binlog 中是否開啟取決於 binlog_row_value_options 。該參數默認為空，不會開啟 Partial update，建議設置為 PARTIAL_JSON。

注意使用 Partial update 的前提條件。

當我們使用 JSON_CONTAINS、MEMBER OF、JSON_OVERLAPS 進行數組相關的操作時，可使用 MySQL 8.0.17 引入的多值索引來加快查詢。

九、參考資料

1. JSON

2. The JSON Data Type

3. JSON Functions

4. Upgrading JSON data stored in TEXT columns

5. Indexing JSON documents via Virtual Columns

6. Partial update of JSON values

7. MySQL 8.0: InnoDB Introduces LOB Index For Faster Updates