Json & JsonB

简介

JSON和JSONB类型在使用上几乎完全一致，两者的区别主要在存储上，json数据类型直接存储输入文本的完全的拷贝，JSONB数据类型以二进制格式进行存储。同时JSONB相较于JSON更高效，处理速度提升非常大，且支持索引。

使用场景

不是所有属性都适合放在JSON中。不合理，不周全的设计会不仅会会影响程序的性能，还会给软件的开发和维护造成困难。因此，我们应该在追求JSON 的灵活性和发挥关系型数据库的功能之间取得平衡。

下面是不适合放在Json中的属性：

  1. 作为重要的选择条件，或用于连接（join）、排序（使用了order by）、去重（使用了distinct）或分组（使用了group by）运算的属性。尽管我们可以通过数据库提供的函数或操作符获取 JSON 中的属性，并通过它进行上述运算，但处理较复杂，性能不佳。

  2. 某个属性的读取和更新频率比其他属性频繁很多，则它们不适合放在同一个 JSON 字段中。因为 JSON 字段占用空间很大，读写代价都很高。因此为提高性能，应将读写频繁的属性作为单独的字段。

  3. 为了提高查询速度而设置的冗余属性。这样的属性通常依赖于某些非主属性，放在JSON中则不符合提高性能的目的，而且不易修改。

  4. 本身定义不合理的，与其他属性重复的，或可能会被弃用的属性。从数据库的层面看，JSON是一个整体，单个属性的缺陷就是整个JSON的缺陷。而且修改或删除这样的有缺陷属性，代价会比修改或删除单独字段的代价高。因此，这一类属性，应当予以删除或改造。

适合放在Json中的属性：

  1. 用户定义的属性，可以放在 JSON 中。

  2. 若属性是一个集合，则可以用JSON数组表示。

  3. 若表中有较大比例的行没有该属性，或该属性为空，则可以将该属性放在JSON字段中。

  4. 对数据库透明，仅由上层程序处理的属性，可以放在JSON字段中。这样的JSON 字段一般很少在数据库中修改，而JSON 作为一个整体在程序中传输。

使用上的注意事项

  1. JSON 结构尽量简单，理想的情况下，合并后的JSON 的键和值分别对应原有的字段和它的值。

  2. 用来合成 JSON 的原字段最好是原子性的，或者对数据库是透明的。这样的字段便于合并，在 JSON 中也容易解析。

  3. 最后，如果你需要用一些非原子性的字段构造一个复杂的 JSON，则应该详细地写出构造的方法步骤，再进行编码。

  4. 增加，修改或删除JSON 字段中的键值对时，应该明确地给定表上的选择条件和JSON 键的路径，从而使我们能够直接通过给定条件获取表中需要更改的行，并能够根据 JSON 键的完整路径来添加，修改或删除键值对。如果新增的值，或者修改后的值依赖于 JSON 中某个键的值，则被依赖的键的路径也应该是明确的。

  5. 修改JSON的结构应该慎重。JSON 字段的结构在确定之后，不应该发生较大变化。

在Mysql中的使用

在 mysql5.7 版本之前， json 数据一般使用数据类型 LONGBLOB 或者 LONGTEXT 存储。

MySQL 5.7 增加了 JSON 数据类型的支持，在之前如果要存储 JSON 类型的数据的话我们只能自己做 JSON.stringify() 和 JSON.parse() 的操作，而且没办法针对 JSON 内的数据进行查询操作，所有的操作必须读取出来 parse 之后进行，非常的麻烦。原生的 JSON 数据类型支持之后，我们就可以直接对 JSON 进行数据查询和修改等操作了，较之前会方便非常多。

为了方便演示先创建一个 user 表，其中 info 字段用来存储用户的基础信息。要将字段定义成 JSON 类型数据非常简单，直接字段名后接 JSON 即可。

1
2
3
4
5
CREATE TABLE user (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  info JSON
);

表创建成功之后就按照经典的 CRUD 数据操作来讲讲怎么进行 JSON 数据类型的操作。

添加数据

添加数据这块是比较简单，不过需要理解 MySQL 对 JSON 的存储本质上还是字符串的存储操作。只是当定义为 JSON 类型之后内部会对数据再进行一些索引的创建方便后续的操作而已。所以添加 JSON 数据的时候需要使用字符串包装。

1
2
mysql> INSERT INTO user (`name`, `info`) VALUES('lilei', '{"sex": "male", "age": 18, "hobby": ["basketball", "football"], "score": [85, 90, 100]}');
Query OK, 1 row affected (0.00 sec)

除了自己拼 JSON 之外，你还可以调用 MySQL 的 JSON 创建函数进行创建。

  • JSON_OBJECT：快速创建 JSON 对象，奇数列为 key，偶数列为 value，使用方法 JSON_OBJECT(key,value,key1,value1)
  • JSON_ARRAY：快速创建 JSON 数组，使用方法 JSON_ARRAY(item0, item1, item2)
1
2
3
4
5
6
7
mysql> INSERT INTO user (`name`, `info`) VALUES('hanmeimei', JSON_OBJECT(
    ->   'sex', 'female', 
    ->   'age', 18, 
    ->   'hobby', JSON_ARRAY('badminton', 'sing'), 
    ->   'score', JSON_ARRAY(90, 95, 100)
    -> ));
Query OK, 1 row affected (0.00 sec)

来看看最终存储到数据库中的数据是什么样的

1
2
3
4
5
6
7
8
mysql> SELECT * FROM `user`;
+----+-----------+-----------------------------------------------------------------------------------------+
| id | name      | info                                                                                    |
+----+-----------+-----------------------------------------------------------------------------------------+
|  1 | lilei     | {"age": 18, "sex": "male", "hobby": ["basketball", "football"], "score": [85, 90, 100]} |
|  2 | hanmeimei | {"age": 18, "sex": "female", "hobby": ["badminton", "sing"], "score": [90, 95, 100]}    |
+----+-----------+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

查询数据

为了更好的支持 JSON 数据的操作，MySQL 提供了一些 JSON 数据操作类的方法。和查询操作相关的方法主要如下：

  • JSON_EXTRACT()：根据 Path 获取部分 JSON 数据，使用方法 JSON_EXTRACT(json_doc, path[, path] ...)
  • ->：JSON_EXTRACT() 的等价写法
  • ->>：JSON_EXTRACT() 和 JSON_UNQUOTE() 的等价写法
  • JSON_CONTAINS()：查询 JSON 数据是否在指定 Path 包含指定的数据，包含则返回1，否则返回0。使用方法 JSON_CONTAINS(json_doc, val[, path])
  • JSON_CONTAINS_PATH()：查询是否存在指定路径，存在则返回1，否则返回0。one_or_all 只能取值 “one” 或 “all”，one 表示只要有一个存在即可，all 表示所有的都存在才行。使用方法 JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
  • JSON_KEYS()：获取 JSON 数据在指定路径下的所有键值。使用方法 JSON_KEYS(json_doc[, path])，类似 JavaScript 中的 Object.keys() 方法。
  • JSON_SEARCH()：查询包含指定字符串的 Paths，并作为一个 JSON Array 返回。查询的字符串可以用 LIKE 里的 ‘%’ 或 ‘_’ 匹配。使用方法 JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])，类似 JavaScript 中的 findIndex() 操作。

我们在这里不对每个方法进行逐个的举例描述，仅提出一些场景举例应该怎么操作。

返回用户的年龄和性别

举这个例子就是想告诉下大家怎么获取 JSON 数据中的部分内容，并按照正常的表字段进行返回。这块可以使用 JSON_EXTRACT 或者等价的 -> 操作都可以。其中根据例子可以看到 sex 返回的数据都带有引号，这个时候可以使用 JSON_UNQUOTE() 或者直接使用 ->> 就可以把引号去掉了。

1
2
3
4
5
6
7
8
mysql> SELECT `name`, JSON_EXTRACT(`info`, '$.age') as `age`, `info`->'$.sex' as sex FROM `user`;
+-----------+------+----------+
| name      | age  | sex      |
+-----------+------+----------+
| lilei     | 18   | "male"   |
| hanmeimei | 16   | "female" |
+-----------+------+----------+
2 rows in set (0.00 sec)

这里我们第一次接触到了 Path 的写法，MySQL 通过这种字符串的 Path 描述帮助我们映射到对应的数据。和 JavaScript 中对象的操作比较类似，通过 . 获取下一级的属性，通过 [] 获取数组元素。

不一样的地方在于需要通过 $ 表示本身，这个也比较好理解。另外就是可以使用 * 和 ** 两个通配符，比如 .* 表示当前层级的所有成员的值，[*] 则表示当前数组中所有成员值。** 类似 LIKE 一样可以接前缀和后缀，比如 a**b 表示的是以 a 开头，b结尾的路径。

返回喜欢篮球的男性用户

1
2
3
4
5
6
7
mysql> SELECT `name` FROM `user` WHERE JSON_CONTAINS(`info`, '"male"', '$.sex') AND JSON_SEARCH(`info`, 'one', 'basketball', null, '$.hobby');
+-------+
| name  |
+-------+
| lilei |
+-------+
1 row in set, 1 warning (0.00 sec)

这个例子就是简单的告诉大家怎么对属性和数组进行查询搜索。其中需要注意的是 JSON_CONTAINS() 查询字符串由于不带类型转换的问题字符串需要使用加上 "" 包裹查询，或者使用 JSON_QUOTE('male') 也可以。

如果你使用的是 MySQL 8 的话，也可以使用新增的 JSON_VALUE() 来代替 JSON_CONTAINS()，新方法的好处是会带类型转换，避免刚才双引号的尴尬问题。不需要返回的路径的话，JSON_SEARCH() 在这里也可以使用新增的 MEMBER OF 或者 JSON_OVERLAPS() 方法替换。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND 'basketball' MEMBER OF(JSON_VALUE(`info`, '$.hobby'));
+-------+
| name  |
+-------+
| lilei |
+-------+
1 row in set (0.00 sec)

mysql> SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball'));
+-------+
| name  |
+-------+
| lilei |
+-------+
1 row in set (0.00 sec)

修改数据

MySQL 提供的 JSON 操作函数中，和修改操作相关的方法主要如下：

  • JSON_APPEND/JSON_ARRAY_APPEND：这两个名字是同一个功能的两种叫法，MySQL 5.7 的时候为 JSON_APPEND，MySQL 8 更新为 JSON_ARRAY_APPEND，并且之前的名字被废弃。该方法如同字面意思，给数组添加值。使用方法 JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

  • JSON_ARRAY_INSERT：给数组添加值，区别于 JSON_ARRAY_APPEND() 它可以在指定位置插值。使用方法 JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

  • JSON_INSERT/JSON_REPLACE/JSON_SET：以上三个方法都是对 JSON 插入数据的，他们的使用方法都为 JSON_[INSERT|REPLACE|SET](json_doc, path, val[, path, val] ...)，不过在插入原则上存在一些差别。

    • JSON_INSERT：当路径不存在才插入
    • JSON_REPLACE：当路径存在才替换
    • JSON_SET：不管路径是否存在

  • JSON_REMOVE：移除指定路径的数据。使用方法 JSON_REMOVE(json_doc, path[, path] ...)

由于 JSON_INSERT, JSON_REPLACE, JSON_SET 和 JSON_REMOVE 几个方法支持属性和数组的操作，所以前两个 JSON_ARRAY 方法用的会稍微少一点。下面我们根据之前的数据继续举几个实例看看。

修改用户的年龄

1
2
3
4
5
6
7
8
9
10
11
mysql> UPDATE `user` SET `info` = JSON_REPLACE(`info`, '$.age', 20) WHERE `name` = 'lilei';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_VALUE(`info`, '$.age') as age FROM `user` WHERE `name` = 'lilei';
+------+
| age  |
+------+
| 20   |
+------+
1 row in set (0.00 sec)

修改用户的爱好

1
2
3
4
5
6
7
8
9
10
11
mysql> UPDATE `user` SET `info` = JSON_ARRAY_APPEND(`info`, '$.hobby', 'badminton') WHERE `name` = 'lilei';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei';
+-----------------------------------------+
| hobby                                   |
+-----------------------------------------+
| ["basketball", "football", "badminton"] |
+-----------------------------------------+
1 row in set (0.00 sec)

JSON_ARRAY_APPEND 在对数组进行操作的时候还是要比 JSON_INSERT 之类的方便的，起码你不需要知道数组的长度。

删除用户的分数

1
2
3
4
5
6
7
8
9
10
11
mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, '$.score[0]') WHERE `name` = 'lilei';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT `name`, JSON_VALUE(`info`, '$.score') as score FROM `user` WHERE `name` = 'lilei';
+-------+-----------+
| name  | score     |
+-------+-----------+
| lilei | [90, 100] |
+-------+-----------+
1 row in set (0.00 sec)

删除这块和之前修改操作类似，没有什么太多需要说的。但是对数组进行操作很多时候我们可能就是想删值，但是却不知道这个值的 Path 是什么。这个时候就需要利用之前讲到的 JSON_SEARCH() 方法，它是根据值去查找路径的。比如说我们要删除 lilei 兴趣中的 badminton 选项可以这么写。

1
2
3
4
5
6
7
8
9
10
11
mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton'))) WHERE `name` = 'lilei';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei';
+----------------------------+
| hobby                      |
+----------------------------+
| ["basketball", "football"] |
+----------------------------+
1 row in set (0.00 sec)

这里需要注意由于 JSON_SEARCH 不会做类型转换，所以匹配出来的路径字符串需要进行 JSON_UNQUOTE() 操作。另外还有非常重要的一点是 JSON_SEARCH 无法对数值类型数据进行查找，也不知道这个是 Bug 还是 Feature。这也是为什么我没有使用 score 来进行举例而是换成了 hobby 的原因。如果数值类型的话目前只能取出来在代码中处理了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT JSON_VALUE(`info`, '$.score') FROM `user` WHERE `name` = 'lilei';
+-------------------------------+
| JSON_VALUE(`info`, '$.score') |
+-------------------------------+
| [90, 100]                     |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SEARCH(`info`, 'one', 90, null, '$.score') FROM `user` WHERE `name` = 'lilei';
+-------------------------------------------------+
| JSON_SEARCH(`info`, 'one', 90, null, '$.score') |
+-------------------------------------------------+
| NULL                                            |
+-------------------------------------------------+
1 row in set (0.00 sec)

在postgres中的使用

根据RFC 7159中的说明，JSON 数据类型是用来存储 JSON（JavaScript Object Notation）数据的。这种数据也可以被存储为text，但是 JSON 数据类型的优势在于能强制要求每个被存储的值符合 JSON 规则。也有很多 JSON 相关的函数和操作符可以用于存储在这些数据类型中的数据

PostgreSQL支持两种 JSON 数据类型：json 和 jsonb。它们几乎接受完全相同的值集合作为输入。两者最大的区别是效率。json数据类型存储输入文本的精准拷贝，处理函数必须在每 次执行时必须重新解析该数据。而jsonb数据被存储在一种分解好的二进制格式中，因为需要做附加的转换，它在输入时要稍慢一些。但是 jsonb在处理时要快很多，因为不需要重新解析。

jsonb支持索引

由于json类型存储的是输入文本的准确拷贝，存储时会空格和JSON 对象内部的键的顺序。如果一个值中的 JSON 对象包含同一个键超过一次，所有的键/值对都会被保留（处理函数会把最后的值当作有效值）。jsonb不保留空格、不保留对象键的顺序并且不保留重复的对象键。如果在输入中指定了重复的键，只有最后一个值会被保留。

官方推荐把JSON 数据存储为jsonb。在把文本 JSON 输入转换成jsonb时，JSON的基本类型RFC 7159 会被映射到原生的 PostgreSQL类型。因此，jsonb数据有一些次要额外约束。 比如：jsonb将拒绝除 PostgreSQL numeric数据类型范围之外的数字，而json则不会。

JSON 基本类型和相应的PostgreSQL类型

JSON 基本类型 PostgreSQL类型 注释
string text 不允许\u0000，如果数据库编码不是 UTF8，非 ASCII Unicode 转义也是这样
number numeric 不允许NaN 和 infinity值
boolean boolean 只接受小写true和false拼写
null (无) SQL NULL是一个不同的概念

json查询语法

在使用JSON文档时，推荐 将JSON 文档存储为固定的结构。（该结构是非强制的，但是有一个可预测的结构会使集合的查询更容易。 ） 设计JSON文档建议：任何更新都在整行上要求一个行级锁。为了减少锁争夺，JSON 文档应该每个表示 一个原子数据（业务规则上的不可拆分，可独立修改的数据）。

下表说明了可以用于JSON & JSONB数据类型的操作符。

操作符 右操作数类型 描述 例子 结果
-> int 获得JSON数组元素（索引从零开始）。 '[{"a":"foo"}, {"b":"bar"}, {"c":"baz"}]'::json->2 {"c":"baz"}
-> text 根据键获得JSON对象的域。 '{"a": {"b":"foo"}}'::json->'a' {"b":"foo"}
->> int 获得JSON数组元素的文本形式。 '[1,2,3]'::json->>2 3
->> text 获得JSON对象域的文本形式。 '{"a":1,"b":2}'::json->>'b' 2
#> text[] 获得在指定路径上的JSON对象。 '{"a": {"b":{"c": "foo"}}}'::json#>'{a,b}' {"c": "foo"}
#>> text[] 获得在指定路径上的JSON对象的文本形式。 '{"a":[1,2,3], "b":[4,5,6]}'::json#>>'{a,2}' 3Jsonb常用的比较操作符

下表说明了可以用于JSONB数据类型的操作符。

操作符 右操作数类型 描述 例子
= jsonb 两个JSON对象的内容是否相等 '[1,2]'::jsonb= '[1,2]'::jsonb
@> jsonb 左边的JSON对象是否包含右边的JSON对象 '{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb
<@ jsonb 左边的JSON对象是否包含于右边的JSON对象 '{"b":2}'::jsonb <@ '{"a":1, "b":2}'::jsonb
? text 指定的字符串是否存在于JSON对象中的key或者字符串类型的元素中 '{"a":1, "b":2}'::jsonb ? 'b'
?| text[] 右值字符串数组是否存在任一元素在JSON对象字符串类型的key或者元素中 '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']
?& text[] 右值字符串数组是否所有元素在JSON对象字符串类型的key或者元素中 '["a", "b"]'::jsonb ?& array['a', 'b']

GIN 索引介绍

JSONB 最常用的是GIN 索引，GIN 索引可以被用来有效地搜索在大量jsonb文档（数据）中出现 的键或者键值对。

GIN(Generalized Inverted Index, 通用倒排索引) 是一个存储对(key, posting list)集合的索引结构，其中key是一个键值，而posting list 是一组出现过key的位置。如(‘hello’, ‘14:2 23:4’)中，表示hello在14:2和23:4这两个位置出现过，在PG中这些位置实际上就是元组的tid(行号，包括数据块ID（32bit）,以及item point(16 bit) )。

在表中的每一个属性，在建立索引时，都可能会被解析为多个键值，所以同一个元组的tid可能会出现在多个key的posting list中。

通过这种索引结构可以快速的查找到包含指定关键字的元组，因此GIN索引特别适用于多值类型的元素搜索，比如支持全文搜索，数组中元素的搜索，而PG的GIN索引模块最初也是为了支持全文搜索而开发的。

PostgreSQL 拥有开放的索引接口，使得PG支持非常丰富的索引方法，例如btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap (greenplum extend)，用户可以根据不同的数据类型，以及查询的场景，选择不同的索引。

虽然简单索引的方法更加灵活（因为它支持有关任意键的查询），但定向的表达式索引更小并且搜索速度比简单索引更快。 尽管jsonb_path_ops操作符类只支持用 @>操作符的查询，但它比起默认的操作符类 jsonb_ops有更客观的性能优势。一个 jsonb_path_ops索引通常也比一个相同数据上的 jsonb_ops要小得多，并且搜索的专一性更好，特 别是当查询包含频繁出现在该数据中的键时。因此，其上的搜索操作 通常比使用默认操作符类的搜索表现更好。

JSONB类型支持GIN, BTree索引。一般情况下，我们会在JSONB类型字段上建GIN索引，语法如下：

1
2
CREATE INDEX idx_name ON table_name USING gin (idx_col);
CREATE INDEX idx_name ON table_name USING gin (idx_col jsonb_path_ops);

说明 在JSONB上创建GIN索引的方式有两种：使用默认的jsonb_ops操作符创建和使用jsonb_path_ops操作符创建。两者的区别在jsonb_ops的GIN索引中，JSONB数据中的每个key和value都是作为一个单独的索引项的，而jsonb_path_ops则只为每个value创建一个索引项。