• MySQL中的JSON

    从5.7.8开始，MySQL开始支持JSON类型，用于存储JSON数据。

    JSON类型的加入模糊了关系型数据库与NoSQL之间的界限，给日常开发也带来了很大的便利。

    这篇文章主要介绍一下MySQL中JSON类型的使用，主要参考MySQL手册：https://dev.mysql.com/doc/refman/8.0/en/

    1. 为什么要用JSON

    自从MySQL添加对JSON的支持之后，一些表结构变更的操作就变得简单了一些。

    1.1 JSON的使用场景

    虽然关系型数据库一直很有效，但是面对需求的不断变化，文档型数据库更加灵活方便。

    MySQL支持JSON之后，模糊了关系型与文档型数据库之间的界限。

    在开发过程中经常会遇见下面几种情况：

    • 表中仅仅小部分数据需要新添加的字段；
    • 当这个新添加的字段很有可能只是临时使用后续会废弃的时候；
    • 当后面还不知道要新添加什么字段但大概率要添加的时候。

    这些时候，使用一个JSON进行存储比较合适，不用更改表结构，非常方便。

    1.2 字符串还是JSON类型

    在还不支持JSON的MySQL 5.7版本之前，没有选择只能使用一个字符串类型存储JSON数据了。

    但是如果数据库支持JSON类型，那么就还是使用JSON类型吧。

    JSON类型相比与使用字符串存储JSON数据有如下的好处：

    • 可以对存储的JSON数据自动校验，不合法的JSON数据插入时会报错；
    • 优化的存储结构。JSON类型将数据转化为内部结构进行存储，使得可以对JSON类型数据进行搜索与局部变更；而对于字符串来说，需要全部取出来再更新。

    2. JSON的增删改查

    这里将简单介绍一下JSON类型的使用，主要是增删改查等操作。

    MySQL中使用utf8mb4字符集以及utf8mb4_bin字符序来处理JSON中的字符串，因此JSON中的字符串时大小写敏感的。

    2.1 创建JSON列

    创建一个JSON类型的列很简单：

    CREATE TABLE videos (
	id int NOT NULL AUTO_INCREMENT,
	ext json NOT NULL,
	PRIMARY KEY (id)
);

    • 1
    • 2
    • 3
    • 4
    • 5

    我们构建了一个表videos，里面有一个JSON类型的ext字段，用于存储一些扩展数据。

    2.2 插入JSON值

    和其它类型一样，使用INSERT来插入数据：

    INSERT INTO videos
VALUES (1, '{"vid": "vid1", "title": "title1", "tags": ["news", "china"], "logo": true}'),
	(2, '{"vid": "vid2", "tags":[], "title": "title2", "logo": false}'),
	(3, '{"vid": "vid3", "title": "title3"}');

    • 1
    • 2
    • 3
    • 4

    来看一下现在表里的数据：

    mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "title": "title3"}                                          |
+----+-----------------------------------------------------------------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    每一个ext都是一个JSON数据。

    2.3 校验JSON

    使用JSON类型的一个好处就是MySQL可以自动检查数据的有效性，避免插入非法的JSON数据。

    2.3.1 JSON合法性校验

    首先需要校验一个值是否是一个合法的JSON，否则插入会失败：

    mysql> insert into videos values (1, '{');
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'videos.ext'.

    • 1
    • 2

    同时还可以使用JSON_VALID()函数查看一个JSON值是否合法：

    mysql> select json_valid('{');
+-----------------+
| json_valid('{') |
+-----------------+
|               0 |
+-----------------+

mysql> select json_valid('{"vid": "vid1"}');
+-------------------------------+
| json_valid('{"vid": "vid1"}') |
+-------------------------------+
|                             1 |
+-------------------------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    2.3.2 JSON模式校验

    如果更进一步，除了值是否是合法JSON外，还需要校验模式，比如JSON值要包含某些字段等。

    这时可以定义一个模式（schema），然后使用JSON_SCHEMA_VALID()或JSON_SCHEMA_VALIDATION_REPORT()函数来校验。

    JSON_SCHEMA_VALID()和JSON_SCHEMA_VALIDATION_REPORT()两个函数是8.0.17版本引入的，5.7版本还没有。

    定义一个模式：

    {
	"id": "schema_for_videos",
	"$schema": "http://json-schema.org/draft-04/schema#",
	"description": "Schema for the table videos",
	"type": "object",
	"properties": {
		"vid": {
			"type": "string"
		},
		"tags": {
			"type": "array"
		},
		"logo": {
			"type": "boolean"
		},
		"title": {
			"type": "string"
		}
	},
	"required": ["title", "tags"]
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    字段含义：

    • id: 模式的唯一ID；
    • $schema: JSON模式校验的标准，应该是这个值保持不变；
    • description: 模式的描述；
    • type: 根元素的类型，MySQL中JSON的根元素还可以是数组（array）；
    • properties: JSON元素的列表，每一个元素都应该描述出来，里面列出了对应的类型；
    • required: 必要的元素。

    在MySQL中定义一个变量：

    mysql> set @schema = '{"id":"schema_for_videos","$schema":"http://json-schema.org/draft-04/schema#","description":"Schema for the table videos","type":"object","properties":{"title":{"type":"string"},"tags":{"type":"array"}},"required":["title","tags"]}';
Query OK, 0 rows affected (0.04 sec)

    • 1
    • 2

    这样就可以使用JSON_SCHEMA_VALID()或JSON_SCHEMA_VALIDATION_REPORT()校验一个JSON是否满足要求了：

    mysql> select json_schema_valid(@schema, '{"title": "", "vid": "", "logo": false, "tags": []}') as 'valid?';
+--------+
| valid? |
+--------+
|      1 |
+--------+

mysql> select json_schema_validation_report(@schema, '{"title": "", "vid": "", "logo": false, "tags": []}') as 'valid?';
+-----------------+
| valid?          |
+-----------------+
| {"valid": true} |
+-----------------+

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    JSON_SCHEMA_VALID()和JSON_SCHEMA_VALIDATION_REPORT()的区别就是后者可以给出不满足要求的地方：

    mysql> select json_schema_valid(@schema, '{"vid": "", "logo": false, "tags": []}') as 'valid?';
+--------+
| valid? |
+--------+
|      0 |
+--------+

mysql> select json_schema_validation_report(@schema, '{"vid": "", "logo": false, "tags": []}') as 'valid?'\G
*************************** 1. row ***************************
valid?: {"valid": false, "reason": "The JSON document location '#' failed requirement 'required' at JSON Schema location '#'", "schema-location": "#", "document-location": "#", "schema-failed-keyword": "required"}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    当然，这两个函数的第二个参数要是一个合法的JSON，不然MySQL会报错：

    mysql> select json_schema_valid(@schema, '{') as 'valid?';ERROR 3141 (22032): Invalid JSON text in argument 2 to function json_schema_valid: "Missing a name for object member." at position 1.

    • 1

    我们还可以将这个模式添加到表的定义上，这样插入数据就可以使用这个模式进行校验了：

    ALTER TABLE videos
ADD CONSTRAINT CHECK (JSON_SCHEMA_VALID('{"id":"schema_for_videos","$schema":"http://json-schema.org/draft-04/schema#","description":"Schema for the table videos","type":"object","properties":{"vid":{"type":"string"},"tags":{"type":"array"},"logo":{"type":"bool"},"title":{"type":"string"}},"required":["title","tags"]}', ext));

    • 1
    • 2

    当然如果表里已经有数据了且不符合这个校验模式，MySQL会报错：

    ERROR 3819 (HY000): Check constraint 'videos_chk_1' is violated.

    • 1

    应该修改原来的数据以满足要求后再添加校验。

    添加之后，新增的数据就会进行校验：

    mysql> INSERT INTO videos VALUES (1, '{"vid": "vid1", "title": "title1", "tags": ["news", "china"], "logo": true}');Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO videos VALUES (2, '{"vid": "vid2", "title": "title2"}');ERROR 3819 (HY000): Check constraint 'videos_chk_1' is violated.

    • 1
    • 2
    • 3

    2.4 JSON的格式化

    使用JSON_PRETTY()函数进行美化输出：

    mysql> select json_pretty(ext) from videos\G
*************************** 1. row ***************************
json_pretty(ext): {
  "vid": "vid1",
  "logo": true,
  "tags": [
    "news",
    "china"
  ],
  "title": "title1"
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    2.5 获取JSON元素

    JSON字段优于JSON字符串的一点就是JSON字段可以直接获取内部的元素而不用获取整个文档。

    MySQL中支持使用JSON_EXTRACT()函数以及->，->>操作符来获取JSON内部的元素：

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

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    使用->：

    mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"}         |
+----+-----------------------------------------------------------------------------+
3 rows in set (0.04 sec)

mysql> select ext->'$.title' from videos;
+----------------+
| ext->'$.title' |
+----------------+
| "title1"       |
| "title2"       |
| "title3"       |
+----------------+
3 rows in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    ->就是JSON_EXTRACT()函数的别名。

    使用JSON_UNQUOTE()函数去掉引号：

    mysql> select json_unquote(ext->'$.title') from videos;
+------------------------------+
| json_unquote(ext->'$.title') |
+------------------------------+
| title1                       |
| title2                       |
| title3                       |
+------------------------------+
3 rows in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    还可以使用->>达到同样的效果（->>就是JSON_UNQUOTE(JSON_EXTRACT(...))的别名）:

    mysql> select ext->>'$.title' from videos;
+-----------------+
| ext->>'$.title' |
+-----------------+
| title1          |
| title2          |
| title3          |
+-----------------+
3 rows in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    2.6 JSONPath

    在获取JSON元素的过程中，我们使用了类似$.title，$.b[1]这样的结构来指定元素，这些就是JSONPath。

    JSONPath使用$符号表示整个JSON文档，后面可以跟着不同的符号表示不同的元素：

    • 一个点号(.)加上key，可以获取指定key的值；
    • [N]获取数组中下标为N的元素（0开始）；
    • [N to M]数组元素还可以指定开头结尾（都包含）；
    • [last] last表示数组中的最后一个元素；
    • [*]获取数组中的所有元素；
    • prefix**suffix获取所有prefix开头suffix结尾的JSONPath。

    以下面的JSON为例：

    {
	"a": "a_value",
	"b": [1, 2, 3, 4, 5],
	"c": true,
	"d": {
		"a": "inner_a",
		"b": [11, 22, "inner_b"]
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • '$'得到整个文档；
    • '$.a'就是"a_value"；
    • '$.b'就是[1, 2, 3, 4, 5]；
    • '$.b[*]'等同于'$.b'；
    • '$.b[2]'得到数组b中的第三个元素3；
    • '$.d.a'得到的就是"inner_a"；
    • '$.d.b[2]'得到的就是"inner_b"；
    • '$.b[1 to 2]'返回[2, 3]；
    • '$.b[last]'返回5；
    • '$.b[last-2 to last-1]'返回[3, 4]；
    • '$**.a'返回的是所有以a结尾的元素组成的数组["a_value", "inner_a"]；
    • '$**.b'就是数组的数组了[[1, 2, 3, 4, 5], [11, 22, "inner_b"]]。

    JSONPath并不仅仅可以用来获取JSON内的元素，涉及到JSON值增删改查的函数基本上都需要一个JSONPath作为参数来指定要操作的元素。

    2.7 搜索JSON元素

    JSON类型的另一个优势就是可以进行搜索。

    搜索可以使用JSON_SEARCH()函数，返回匹配的JSONPath。

    JSON_SEARCH()函数原型如下：

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

    • 1

    其中前三个是必须参数：

    • json_doc: 一个有效的JSON文档；
    • one_or_all: 字符串，必须是'one'或'all'，用于指定匹配返回的个数，如果是'one'的话只返回匹配的第一个，否则全部返回；
    • search_str: 就是需要搜索的值，目前只支持字符串搜索，同时还可以添加%或_来模糊匹配；

    后两个是可选参数：

    • escape_char: 转义字符，默认是\；如果不指定或为NULL的话，也是\;否则，这个参数只能为空（此时还是\）或者一个字符（指定多个会报错）；
    • path: 指定了开始搜索的位置，如果没有的话就是整个文档。

    接下来以下面这个JSON文档为例看一下如何进行搜索：

    {
	"a": "a_value",
	"b": ["1", "2", "3", "4", "5"],
	"c": true,
	"d": {
		"a": "a_value",
		"b": ["1", "2", "bvalue"]
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • json_search(@j, 'one', 'a_value')返回"$.a"；
    • json_search(@j, 'all', 'a_value')返回["$.a", "$.d.a"]；
    • json_search(@j, 'all', '1')返回["$.b[0]", "$.d.b[0]"]；
    • json_search(@j, 'all', '%_value')返回["$.a", "$.d.a", "$.d.b[2]"]；
    • json_search(@j, 'all', '%\_value')返回["$.a", "$.d.a"]，注意和上一个的区别；
    • json_search(@j, 'all', '%|_value', '|')指定转义符，返回["$.a", "$.d.a"]；
    • json_search(@j, 'all', '%|_value', '|', '$.a')指定了开始搜索的位置，返回"$.a"，没有匹配$.d.a；

    接下来，我们就可以在WHERE中使用JSON_SEARCH()了。

    还是之前的videos表：

    mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"}         |
+----+-----------------------------------------------------------------------------+
3 rows in set (0.04 sec)

mysql> select * from videos where json_search(ext, 'all', 'title2');
+----+---------------------------------------------------------------+
| id | ext                                                           |
+----+---------------------------------------------------------------+
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"} |
+----+---------------------------------------------------------------+
1 row in set, 1 warning (0.04 sec)

mysql> select * from videos where json_search(ext, 'all', 'food', '', '$.tags');
+----+---------------------------------------------------------------------+
| id | ext                                                                 |
+----+---------------------------------------------------------------------+
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"} |
+----+---------------------------------------------------------------------+
1 row in set, 1 warning (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    还可以使用->操作符来搜索：

    mysql> select ext from videos where ext->'$.logo' = true;
+------------------------------------------------------------------------------------------------+
| ext                                                                                            |
+------------------------------------------------------------------------------------------------+
| {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1", "protected": true} |
+------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2.8 JSON中插入新元素

    MySQL中有几个函数可以支持向JSON中新增元素：

    • JSON_INSERT()
    • JSON_ARRAY_APPEND()
    • JSON_ARRAY_INSERT()

    这几个函数支持就地更新，而不是取出JSON文档更改后全量覆盖。

    使用JSON_INSERT()函数新增元素：

    update videos set ext = json_insert(ext, '$.protected', true);

    • 1

    如果要增加的元素已经有了的话，则没有变化。

    JSON_ARRAY_APPEND()函数可以向数组中追加元素：

    update videos set ext = json_array_append(ext, '$.tags', 'tech') where json_search(ext, 'all', 'title2', '', '$.title');

    • 1

    这里同时使用了JSON_SEARCH()进行匹配。

    JSON_ARRAY_INSERT()函数可以在数组的指定位置中添加元素：

    update videos set ext=json_array_insert(ext, '$.tags[1]', 'beijing') where ext->'$.vid' = 'vid1';

    • 1

    结果：

    mysql> select ext from videos where ext->'$.vid' = 'vid1';
+-----------------------------------------------------------------------------------------------------------+
| ext                                                                                                       |
+-----------------------------------------------------------------------------------------------------------+
| {"vid": "vid1", "logo": true, "tags": ["news", "beijing", "china"], "title": "title1", "protected": true} |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2.9 更新JSON元素

    使用JSON_REPLACE()或JSON_SET()函数来更新JSON中的元素。

    JSON_REPLACE()函数可以用来更新元素的值：

    update videos set ext = json_replace(ext, '$.protected', false) where ext->'$.vid' = 'vid1';

    • 1

    不过如果JSON中没有要更新的key，那么就什么也不做。

    JSON_SET()除了可以更新元素的值之外，如果指定的元素不存在，还可以添加：

    update videos set ext = json_set(ext, '$.size', 100) where ext->'$.vid' = 'vid1';

    • 1

    2.10 删除JSON元素

    使用JSON_REMOVE()函数可以删除JSON中的元素：

    update videos set ext = json_remove(ext, '$.size') where ext->'$.vid' = 'vid1';

update videos set ext = json_remove(ext, '$.tags[1]') where ext->'$.vid' = 'vid1';

    • 1
    • 2
    • 3

    JSON_REMOVE()函数可以指定多个JSONPath来删除多个元素，这时MySQL是从左到右一个个删除的。

    这样即使是相同的JSONPath但是顺序不一样，结果就会不一样：

    mysql> select json_remove('{"a": [1,2,3,4,5]}', '$.a[2]', '$.a[3]');
+-------------------------------------------------------+
| json_remove('{"a": [1,2,3,4,5]}', '$.a[2]', '$.a[3]') |
+-------------------------------------------------------+
| {"a": [1, 2, 4]}                                      |
+-------------------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_remove('{"a": [1,2,3,4,5]}', '$.a[3]', '$.a[2]');
+-------------------------------------------------------+
| json_remove('{"a": [1,2,3,4,5]}', '$.a[3]', '$.a[2]') |
+-------------------------------------------------------+
| {"a": [1, 2, 5]}                                      |
+-------------------------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    2.11 JSON合并

    MySQL中支持将两个JSON文档合并成一个文档。可以通过下面的两个函数来完成：

    • JSON_MERGE_PATCH()：相当于第二个参数更新第一个参数；
    • JSON_MERGE_PRESERVE()：尽可能地保留两个参数的元素。

    这两个函数有很大的不同，使用的时候一定要注意。

    2.11.1 JSON_MERGE_PATCH

    函数接收至少两个参数，如果多于两个参数的话，那么就前两个合并的结果与后一个进行合并。

    下面假设有两个参数进行讨论，多于两个的也是类似的。

    如果有一个参数是NULL，那么结果就是NULL：

    mysql> select json_merge_patch('{"a": 1, "b": [1,2]}', null);
+------------------------------------------------+
| json_merge_patch('{"a": 1, "b": [1,2]}', null) |
+------------------------------------------------+
| NULL                                           |
+------------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch(null, '{"b": null}');
+---------------------------------------+
| json_merge_patch(null, '{"b": null}') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    如果第一个参数不是object，那么结果就相当于一个空的object和第二个参数合并，其实就是第二个参数：

    mysql> select json_merge_patch('{}', '{"a": "a"}');
+--------------------------------------+
| json_merge_patch('{}', '{"a": "a"}') |
+--------------------------------------+
| {"a": "a"}                           |
+--------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('{}', '"a"');
+-------------------------------+
| json_merge_patch('{}', '"a"') |
+-------------------------------+
| "a"                           |
+-------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('[]', '"a"');
+-------------------------------+
| json_merge_patch('[]', '"a"') |
+-------------------------------+
| "a"                           |
+-------------------------------+
1 row in set (0.04 sec)

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

    如果第二个参数是数组（array），那么结果还是第二个参数：

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    接下来就是两个参数都是object的情况了。

    合并的结果包含第一个参数有而第二个参数没有的那些元素：

    mysql> select json_merge_patch('{"a": 1}', '{}');
+------------------------------------+
| json_merge_patch('{"a": 1}', '{}') |
+------------------------------------+
| {"a": 1}                           |
+------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    也包含第一个参数没有而第二个有的元素（除了值是null的）：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    如果两个参数里都有，那么合并的结果就是两个值递归合并的结果：

    mysql> select json_merge_patch('{"a": 1}', '{"a": 2}');
+------------------------------------------+
| json_merge_patch('{"a": 1}', '{"a": 2}') |
+------------------------------------------+
| {"a": 2}                                 |
+------------------------------------------+
1 row in set (0.04 sec)

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

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31

    这三个例子中，结果就像是第二个参数的值覆盖了第一个，这是因为这几个例子中两个参数a所对应的值不都是object，结果就是第二个参数a的值。

    下面的例子展示了递归合并的结果：

    mysql> select json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"d": 9}}');
+------------------------------------------------------------+
| json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"d": 9}}') |
+------------------------------------------------------------+
| {"a": {"c": [1, 2], "d": 9}}                               |
+------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> select json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"c": 9}}');
+------------------------------------------------------------+
| json_merge_patch('{"a": {"c": [1,2]}}', '{"a": {"c": 9}}') |
+------------------------------------------------------------+
| {"a": {"c": 9}}                                            |
+------------------------------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    如果第二个参数的元素值是null，那么结果里是不含这个元素的：

    mysql> select json_merge_patch('{"a": 1}', '{"b": null}');
+---------------------------------------------+
| json_merge_patch('{"a": 1}', '{"b": null}') |
+---------------------------------------------+
| {"a": 1}                                    |
+---------------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    使用这个特性可以删除第一个参数的元素，就像JSON_REMOVE()一样：

    mysql> select json_merge_patch('{"a": 1, "b": [1,2]}', '{"b": null}');
+---------------------------------------------------------+
| json_merge_patch('{"a": 1, "b": [1,2]}', '{"b": null}') |
+---------------------------------------------------------+
| {"a": 1}                                                |
+---------------------------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2.11.2 JSON_MERGE_PRESERVE

    JSON_MERGE_PRESERVE()函数也是合并两个或多个JSON，但是和JSON_MERGE_PATCH()不同在于第二个参数的元素并不会覆盖第一个参数的元素。

    首先如果有一个参数是NULL，那么救过就是NULL。

    相邻的数组合并成一个数组：

    mysql> select json_merge_preserve('[1,2]', '["a", "b"]');
+--------------------------------------------+
| json_merge_preserve('[1,2]', '["a", "b"]') |
+--------------------------------------------+
| [1, 2, "a", "b"]                           |
+--------------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    相邻的两个object合并成一个object：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    标量值会包装成数组，然后按照数组的方式合并：

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

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    相邻的数组和object合并，先将object包装成一个数组，然后两个数组合并：

    mysql> select json_merge_preserve('[1, 2]', '{"id": 47}');
+---------------------------------------------+
| json_merge_preserve('[1, 2]', '{"id": 47}') |
+---------------------------------------------+
| [1, 2, {"id": 47}]                          |
+---------------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    2.12 其它

    MySQL还有很多有用的函数用于操作JSON类型，这里简单介绍JSON_TYPE, JSON_LENGTH, 和JSON_STORAGE_SIZE等函数，其余函数可以参考MySQL文档：https://dev.mysql.com/doc/refman/8.0/en/json-functions.html

    JSON_TYPE返回JSON元素的类型，包括object, array, null以及其他的标量类型：

    mysql> select json_type('{}'), json_type('[]'), json_type('true'), json_type(null), json_type('"a"');
+-----------------+-----------------+-------------------+-----------------+------------------+
| json_type('{}') | json_type('[]') | json_type('true') | json_type(null) | json_type('"a"') |
+-----------------+-----------------+-------------------+-----------------+------------------+
| OBJECT          | ARRAY           | BOOLEAN           | NULL            | STRING           |
+-----------------+-----------------+-------------------+-----------------+------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    JSON_LENGTH函数返回元素的个数：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    JSON_STORAGE_SIZE函数返回JSON数据所占用的字节数：

    mysql> select json_storage_size('{"a": true}'), char_length('{"a": true}');
+----------------------------------+----------------------------+
| json_storage_size('{"a": true}') | char_length('{"a": true}') |
+----------------------------------+----------------------------+
|                               13 |                         11 |
+----------------------------------+----------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    JSON类型所占用的空间大致和LONGBLOB或LONGTEXT一样。不过由于有一些元数据，可能会稍微大一些。

    3. JSON的高级用法

    前面我们介绍了MySQL中JSON类型的一些基本操作，MySQL中对JSON类型的支持还可以有一些更高级的玩法，比如关系型数据与JSON数据的相互转换，甚至可以把MySQL当做一个文档型数据库来使用。

    3.1 关系型数据转JSON

    MySQL中有一些函数支持将关系型数据转换成JSON数据：

    • JSON_OBJECT
    • JSON_ARRAY
    • JSON_OBJECTAGG
    • JSON_ARRAYAGG

    JSON_OBJECT函数可以将多个键值对拼装成一个object：

    mysql> select json_pretty(json_object("a", 1, "b", true, "null", null))\G
*************************** 1. row ***************************
json_pretty(json_object("a", 1, "b", true, "null", null)): {
  "a": 1,
  "b": true,
  "null": null
}
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    如果键值对数量不对的话会报错：

    mysql> select json_pretty(json_object("a", 1, "b", true, "null"))\G
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'

    • 1
    • 2

    JSON_ARRAY函数将所有的参数合并成一个数组：

    mysql> select json_array(1,1,"a",null,true,curtime());
+--------------------------------------------+
| json_array(1,1,"a",null,true,curtime())    |
+--------------------------------------------+
| [1, 1, "a", null, true, "17:38:39.000000"] |
+--------------------------------------------+
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    两个函数组合使用就可以构建一个复杂的JSON数据了：

    mysql> select json_pretty(json_object('example', 'a complex example', 'user', json_object('name', 'valineliu', 'tags', json_array(1,2)), 'books', json_array('a','b'))) as r\G
*************************** 1. row ***************************
r: {
  "user": {
    "name": "valineliu",
    "tags": [
      1,
      2
    ]
  },
  "books": [
    "a",
    "b"
  ],
  "example": "a complex example"
}
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    JSON_OBJECTAGG和JSON_ARRAYAGG两个函数可以通过GROUP BY返回更高级的数据。

    JSON_OBJECTAGG可以指定一个关系型表的两个字段作为构建JSON的键值对。

    比如一个表是这样的：

    mysql> select * from r_videos;
+----+------+---------+
| id | size | title   |
+----+------+---------+
|  1 |  100 | video 1 |
|  2 |  200 | video 2 |
|  3 |  300 | video 3 |
+----+------+---------+
3 rows in set (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    指定title为key，size为value构建一个JSON：

    mysql> select json_pretty(json_objectagg(title, size)) as size from r_videos\G
*************************** 1. row ***************************
size: {
  "video 1": 100,
  "video 2": 200,
  "video 3": 300
}
1 row in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    JSON_ARRAYAGG函数可以将一对多的关系转换成一个JSON数组。比如下面的表：

    mysql> select * from r_videos;
+----+---------+---------+------+
| id | user_id | title   | size |
+----+---------+---------+------+
|  1 |     100 | title 1 | 1000 |
|  2 |     100 | title 2 | 2000 |
|  3 |     200 | title 3 | 3000 |
|  4 |     300 | title 4 | 4000 |
|  5 |     300 | title 5 | 5000 |
|  6 |     300 | title 6 | 6000 |
+----+---------+---------+------+
6 rows in set (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    下面的语句可以将这个关系型表转换成一个user_id为key，title和size构成的object数组为value的JSON：

    mysql> select json_pretty(json_object('user_id', user_id, 'videos', json_arrayagg(json_object('title', title, 'size', size)))) as videos from r_videos group by user_id\G
*************************** 1. row ***************************
videos: {
  "videos": [
    {
      "size": 1000,
      "title": "title 1"
    },
    {
      "size": 2000,
      "title": "title 2"
    }
  ],
  "user_id": 100
}
*************************** 2. row ***************************
videos: {
  "videos": [
    {
      "size": 3000,
      "title": "title 3"
    }
  ],
  "user_id": 200
}
*************************** 3. row ***************************
videos: {
  "videos": [
    {
      "size": 4000,
      "title": "title 4"
    },
    {
      "size": 5000,
      "title": "title 5"
    },
    {
      "size": 6000,
      "title": "title 6"
    }
  ],
  "user_id": 300
}
3 rows in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44

    3.2 JSON转表格

    可以使用JSON_TABLE函数将一个JSON转换成关系型数据。

    先看一个简单的例子：

    mysql> select * from json_table('{"null": null, "title": "hello json", "size": 1}', '$' columns(title varchar(32) path '$.title' error on error, size int path '$.size')) as jt;
+------------+------+
| title      | size |
+------------+------+
| hello json |    1 |
+------------+------+
1 row in set (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    JSON_TABLE函数有两个参数，第一个参数是一个JSON文档，第二个参数就是列定义。

    列定义前面的JSONPath指定了开始解析的位置，列定义里每一个列都指定了列名、类型以及要获取值的JSONPath，多个列定义用,分割。

    下面的例子将一个含有数组的JSON展开成一个一对多的关系型数据。

    原始数据如下：

    mysql> select id, json_pretty(ext) as ext from videos\G
*************************** 1. row ***************************
 id: 1
ext: {
  "vid": "vid1",
  "logo": true,
  "tags": [
    "news",
    "china"
  ],
  "title": "title1",
  "protected": false
}
*************************** 2. row ***************************
 id: 2
ext: {
  "vid": "vid2",
  "logo": false,
  "tags": [
    "tech"
  ],
  "title": "title2",
  "protected": true
}
*************************** 3. row ***************************
 id: 3
ext: {
  "vid": "vid3",
  "logo": false,
  "tags": [
    "food",
    "asian",
    "china"
  ],
  "title": "title3",
  "protected": true
}
3 rows in set (0.03 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38

    其中每一行数据中都有一个数组类型的tags。现在想把这个一对多的数据展开成多行数据：

    mysql> select v.id, jt.* from videos v, json_table(v.ext, '$' columns (title varchar(32) path '$.title', nested path '$.tags[*]' columns (tag varchar(32) path '$'))) as jt;
+----+--------+-------+
| id | title  | tag   |
+----+--------+-------+
|  1 | title1 | news  |
|  1 | title1 | china |
|  2 | title2 | tech  |
|  3 | title3 | food  |
|  3 | title3 | asian |
|  3 | title3 | china |
+----+--------+-------+
6 rows in set (0.04 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    这里对于tag字段的定义使用了nested path。

    3.3 通过JSON将MySQL作为文档型数据库

    通过MySQL Shell甚至可以将MySQL当做一个文档型数据库。

    可以参考https://dev.mysql.com/doc/mysql-shell/8.0/en/ 了解更多关于MySQL Shell的信息。

    本质上还是使用表来存储数据的，比如下面的表：

    CREATE TABLE `MyCollection` ( 
  `doc` json DEFAULT NULL, 
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote( json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL, 
  `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL, 
  PRIMARY KEY (`_id`), 
  CONSTRAINT `$val_strict_2190F99D7C6BE98E2C1EFE4E110B46A3D43C9751` 
  CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    通过mysqlsh连接数据库之后，通过X DevAPI可以像操作MongoDB一样操作上面这个表：

    MyCollection = session.getDefaultSchema().getCollection('MyCollection')

    • 1

    X DevAPI中的Collection类定义了add, find, modify, remove等函数支持增删改查。

    添加数据：

    MyCollection.add({"document": "one"}).add([{"document": "two"}, {"document": "three"}]).add(mysqlx.expr('{"document": "four"}'))

    • 1

    像MongoDB一样find：

    MyCollection.find()
{
	"_id": "000060d5ab750000000000000012",
	"document": "one"
}
{
	"_id": "000060d5ab750000000000000013",
	"document": "two"
}
{
	"_id": "000060d5ab750000000000000014",
	"document": "three"
}
{
	"_id": "000060d5ab750000000000000015",
	"document": "four"
}
4 documents in set (0.0007 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    这里仅仅是简单介绍了一下有意思的玩法，更多关于X DevAPI的信息，请参考https://dev.mysql.com/doc/x-devapi-userguide/en/

    4. orm对JSON的支持

    上面都是从mysql client的角度来使用JSON类型的，在我们的程序中使用更多的还是各种orm。

    这里简单介绍一下orm对于MySQL JSON类型的支持，由于个人原因，这里仅仅列出go语言的两个orm：xorm和gorm。

    不过好像对JSON的支持都不是很丰富。

    其余orm以及其余语言参考各自的文档。

    4.1 xorm

    目前我还没有发现xorm支持JSON类型，也可能是我漏掉了，如果哪位大佬知道的话，感谢补充~

    4.2 gorm

    gorm通过额外的包datatypes来支持JSON类型：

    import "gorm.io/datatypes"

type User struct {
  gorm.Model
  Name       string
  Attributes datatypes.JSON
}

db.Create(&User{
  Name:       "jinzhu",
  Attributes: datatypes.JSON([]byte(`{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "orgs": {"orga": "orga"}}`)),
}

// Query user having a role field in attributes
db.First(&user, datatypes.JSONQuery("attributes").HasKey("role"))
// Query user having orgs->orga field in attributes
db.First(&user, datatypes.JSONQuery("attributes").HasKey("orgs", "orga"))

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    但datatypes目前对JSON类型的支持还是很弱，仅仅支持查找与搜索，更加有用的更新还是没有的：

    import "gorm.io/datatypes"

type UserWithJSON struct {
	gorm.Model
	Name       string
	Attributes datatypes.JSON
}

DB.Create(&User{
	Name:       "json-1",
	Attributes: datatypes.JSON([]byte(`{"name": "jinzhu", "age": 18, "tags": ["tag1", "tag2"], "orgs": {"orga": "orga"}}`)),
}

// Check JSON has keys
datatypes.JSONQuery("attributes").HasKey(value, keys...)

db.Find(&user, datatypes.JSONQuery("attributes").HasKey("role"))
db.Find(&user, datatypes.JSONQuery("attributes").HasKey("orgs", "orga"))
// MySQL
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`, '$.role') IS NOT NULL
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`, '$.orgs.orga') IS NOT NULL

// PostgreSQL
// SELECT * FROM "user" WHERE "attributes"::jsonb ? 'role'
// SELECT * FROM "user" WHERE "attributes"::jsonb -> 'orgs' ? 'orga'


// Check JSON extract value from keys equal to value
datatypes.JSONQuery("attributes").Equals(value, keys...)

DB.First(&user, datatypes.JSONQuery("attributes").Equals("jinzhu", "name"))
DB.First(&user, datatypes.JSONQuery("attributes").Equals("orgb", "orgs", "orgb"))
// MySQL
// SELECT * FROM `user` WHERE JSON_EXTRACT(`attributes`, '$.name') = "jinzhu"
// SELECT * FROM `user` WHERE JSON_EXTRACT(`attributes`, '$.orgs.orgb') = "orgb"

// PostgreSQL
// SELECT * FROM "user" WHERE json_extract_path_text("attributes"::json,'name') = 'jinzhu'
// SELECT * FROM "user" WHERE json_extract_path_text("attributes"::json,'orgs','orgb') = 'orgb'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39

    参考https://gorm.io/docs/v2_release_note.html#DataTypes-JSON-as-example , https://github.com/go-gorm/datatypes

    5. 一些思考

    目前来看，orm对于JSON的支持还不是很丰富，而上面的绝大多数篇幅都是mysql客户端中操作JSON字段的方法，在我们的程序中通过orm操作JSON字段还不是很方便。

    在使用JSON类型的时候，我更多地是把里面的元素当做一个候选字段。

    比如今天来了一个需求需要添加一个字段，我会将这个字段添加到JSON类型字段中，满足可以将数据保存在一条记录中增加数据局部性，而不用在别的地方获取这些数据。

    随着产品的进化与需求的变更，之前添加的字段有的变得没用了，那么就可以后续删除这个元素；而有的字段由于变得更加重要，可以把它提升为一个关系型的字段。

    到底是放在JSON中还是添加一个字段，这个就看具体的使用了。如果这个字段经常使用，读取写入还有搜索，那么添加到一个新的字段还是比较方便的。不过好像添加为一个虚拟字段也是很有用。

    当JSON变得巨大的时候，没准可以考虑使用真正的文档型数据库了，比如MongoDB。

  • 相关阅读:
    交叉编译curl，实现c++的http post
    hub.docker访问不了的问题(一步解决)
    深度对话｜Sui在商业技术堆栈中的地位
    优雅停止 SpringBoot 服务，拒绝 kill -9 暴力停止
    Undefined reference to pthread_create in Linux
    c# 值类型和引用类型的区别
    HTML中的pre标签
    C : DS顺序表--合并操作
    golang slice/array无重复取随机内容
    Numpy入门[16]——choose函数实现条件筛选
  • 原文地址：https://blog.csdn.net/u012877472/article/details/126534123