PostgreSQL JSONB 使用入门

Photo by Tobias Fischer^[9] on Unsplash^[10]

json 类型

说明

根据RFC 7159^[1]中的说明，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^[1] ）会被映射到原生的 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 输入输出语法

-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、true、false或者null
SELECT '5'::json;

-- 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT '[1, 2, "foo", null]'::json;

-- 包含键值对的对象
-- 注意对象键必须总是带引号的字符串
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- 数组和对象可以被任意嵌套
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

-- "->" 通过键获得 JSON 对象域 结果为json对象
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' as nickname;
 nickname
-------------
 "goodspeed"
(1 row)

-- "->>" 通过键获得 JSON 对象域 结果为text 
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' as nickname;
 nickname
-----------
 goodspeed
 
-- "->" 通过键获得 JSON 对象域 结果为json对象
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' as nickname;
 nickname
-------------
 "goodspeed"
(1 row)

-- "->>" 通过键获得 JSON 对象域 结果为text 
select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' as nickname;
 nickname
-----------
 goodspeed
(1 row)

当一个 JSON 值被输入并且接着不做任何附加处理就输出时， json会输出和输入完全相同的文本，而jsonb 则不会保留语义上没有意义的细节

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

json 查询语法

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

常用的比较操作符

操作符	描述
<	小于
>	大于
<=	小于等于
>=	大于等于
=	等于
<> or !=	不等于

这些常用的比较操作符只对jsonb 有效，而不适用于json

包含和存在

json 数据查询（适用于jsonb）

-> 和 ->> 操作符

使用 ->> 查出的数据为text 使用 -> 查出的数据为json 对象

-- nickname 为 gs 的用户 这里使用 ->> 查出的数据为text，所以匹配项也应该是text
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' = 'gs';
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';

-- 使用 -> 查询，会抛出错误，这里无论匹配项是text类型的 'gs'  还是 json 类型的 '"gs"'::json都会抛出异常，json 类型不支持 等号（=）操作符
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' = '"gs"';
ERROR:  operator does not exist: json = unknown

select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python"