MySQL 操作 JSON 数据类型

MySQL 从 v5.7.8 开始支持 JSON 数据类型。

JSON 数据类型和传统数据类型的操作还是有很大的差别，需要单独学习掌握。好在 JSON 数据类型的学习成本不算太高，只是在 SQL 语句中扩展了 JSON 函数，操作 JSON 数据类型主要是对函数的学习。

新建数据库

CREATE TABLE t(id INT, data json, PRIMARY KEY (id));

插入数据

新增 JSON 文档

函数	描述
JSON_ARRAY	创建 JSON 数组
JSON_OBJECT	创建 JSON 对象

插入数据可以通过字符串的方式直接插入或者用 JSON_ARRAY 和 JSON_OBJECT 函数来构造数据。

插入数组

INSERT INTO t (id, data) VALUES (1, '[1, 2, 3]');
INSERT INTO t (id, data) VALUES (2, JSON_ARRAY(1, 2, 3));

插入对象

INSERT INTO t (id, data) VALUES (3, '{"a": 1, "b": 2}');
INSERT INTO t (id, data) VALUES (4, JSON_OBJECT("a", 1, "b", 2, "c", 3));
INSERT INTO t (id, data) VALUES (5, '{"a": "abc", "b": {"c": "dad"}, "c": {"b": "aaa"}}');

修改数据

修改 JSON 文档

函数	描述
JSON_SET	当 Key 存在时修改，当 Key 不存在时新增
JSON_INSERT	当 Key 不存在时新增，当 Key 存在时不做任何变动
JSON_REPLACE	当 Key 存在时更新，当 Key 不存在时不做任何变动
JSON_ARRAY_INSERT	在数组末尾处追加新元素
JSON_ARRAY_APPEND	在数组指定下标处插入新元素
JSON_MERGE_PATCH	合并 JSON 数组或对象
JSON_MERGE_PRESERVE	合并 JSON 数组或对象

在 JSON_SET 中 $ 表示整个 JSON 文档，通过指定对象属性或数组下标的方式修改数据，当 key 存在时修改反之则新增。

UPDATE t SET data = '[1, 20, 30]' WHERE id = 1;
UPDATE t SET data = JSON_SET(data, '$.c', 30, '$.d', 40) WHERE id = 4;
UPDATE t SET data = JSON_INSERT(data, '$.c', 3) WHERE id = 4;
UPDATE t SET data = JSON_REPLACE(data, '$.c', 3) WHERE id = 4;
UPDATE t SET data = JSON_ARRAY_INSERT(data, '$[0]', 0) WHERE id = 1;
UPDATE t SET data = JSON_ARRAY_APPEND(data, '$', 4) WHERE id = 1;

JSON_MERGE_PATCH 和 JSON_MERGE_PRESERVE 都对 JSON 数组或对象进行合并操作，二者用法相同只是合并逻辑有所不同，具体的详情和细节可在文末参考链接中寻找。

删除数据

删除 JSON 文档

函数	描述
JSON_REMOVE	当 Key 存在时删除，当 Key 不存在时不做任何变动

UPDATE t SET data = JSON_REMOVE(data, '$.c', '$.d') WHERE id = 4;

查询数据

查询 JSON 文档

函数	描述
JSON_CONTAINS	判断源数据是否包含某个 JSON 数组或对象，包含返回 1， 否则返回 0
JSON_CONTAINS_PATH	判断源数据某个路径下是否包含某个 JSON 数组或对象，包含返回 1， 否则返回 0
JSON_SEARCH	指定关键字搜索 JSON 数组或对象，可以限制查找范围条件，支持 %、_、*、** 通配符，结果返回为 JSON 值的路径

SELECT JSON_CONTAINS(data, '{"b": 2}') FROM t WHERE id = 3;  // 1
SELECT JSON_CONTAINS(data, '{"c": 3}') FROM t WHERE id = 3;  // 0

第二个参数是控制返回数据的条数，参数如果是 one 只返回一条，参数如果是 all 则返回全部。

SELECT JSON_CONTAINS_PATH(data, 'one', '$.b', '$.c') FROM t WHERE id = 3;  // 1
SELECT JSON_CONTAINS_PATH(data, 'all', '$.b', '$.c') FROM t WHERE id = 3;  // 0

和 LIKE 一样，在字符串关键字的通过用 % 和 _ 在所有节点的值中匹配。

SELECT JSON_SEARCH(data, 'one', '%a%') FROM t WHERE id = 5;  // $.a
SELECT JSON_SEARCH(data, 'all', '%a%') FROM t WHERE id = 5;  // ["$.a", "$.b.c", "$.c.b"]

还可以对条件限制查找范围。

SELECT JSON_SEARCH(data, 'all', '%a%', NULL, '$.b') FROM t WHERE id = 5;  // "$.b.c"

查找范围还可使用通配符，** 是递归匹配所有节点下的值。

SELECT JSON_SEARCH(data, 'all', '%a%', NULL, '$**.b') FROM t WHERE id = 5;  // ["$.b.c", "$.c.b"]

在实际开发中，一般 JSON_SEARCH 用的是最多的，而且大部分情况是作为搜索条件出现的，例如：

SELECT * FROM `templates_data` WHERE `template_id`=4 AND data -> '$**.value' LIKE '%内蒙古%'
SELECT * FROM `templates_data` WHERE `template_id`=4 AND JSON_SEARCH(data, 'all', '%内蒙古%', null, '$**.value')
SELECT * FROM `templates_data` WHERE `template_id`=4 AND JSON_CONTAINS(data, JSON_OBJECT('value', '内蒙古'))

**注意：**只有 JSON_EXTRACT 和 JSON_SEARCH 中的 path 才支持通配符。

操作文档

操作 JSON 文档

函数	描述
JSON_QUOTE	将 String 类型转成 JSON 类型
JSON_UNQUOTE	将 JSON 类型转成 String 类型
JSON_KEYS	以数组的方式返回 JSON 文档中的 Key
JSON_EXTRACT	以字符串或数组的方式返回 JSON 文档中的 Value，支持通配符
JSON_LENGTH	返回 JSON 文档的长度
JSON_DEPTH	返回 JSON 文档的最大深度
JSON_TYPE	返回 JSON 文档值的类型
JSON_VALID	判断 JSON 文档是否合法

SELECT JSON_QUOTE('{"a": 1, "b": 2, "c": 3}');          // "{\"a\": 1, \"b\": 2, \"c\": 3}"
SELECT JSON_UNQUOTE('{"a": 1, "b": 2, "c": 3}');        // {"a": 1, "b": 2, "c": 3}
SELECT JSON_KEYS('{"a": 1, "b": 2, "c": 3}');           // ["a", "b", "c"]
SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": 3}', '$.b'); // 2
SELECT JSON_LENGTH('{"a": 1, "b": 2, "c": 3}', '$.b');  // 1
SELECT JSON_DEPTH('{"a": 1, "b": 2, "c": 3}');          // 2
SELECT JSON_TYPE('{"a": 1, "b": 2, "c": 3}');           // OBJECT
SELECT JSON_VALID('{"a": 1, "b": 2, "c": 3}');          // 1

参考链接：
https://www.sjkjc.com/mysql-ref/json_array/
https://www.cnblogs.com/xyj179/p/11451593.html