PostgreSQL中JSONB的使用

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 输入输出语法

-- 简单标量/基本值
-- 基本值可以是数字、带引号的字符串、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)

包含和存在

-> 和 ->> 操作符

-- 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", "golang", "db"]}'::json->'nickname' = '"gs"'::json;
ERROR:  operator does not exist: json = json

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

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

#> 和 #>> 操作符

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

select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' as tag;
   tag
----------
 "python"

select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>>'{tags,0}' as tag;
  tag
--------
 python

select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>'{tags,0}' = '"python"';
 ?column?
----------
 t
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>>'{tags,0}' = 'python';
 ?column?
----------
 t

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

jsonb 数据查询（不适用于 json）

@>操作符

-- nickname 为 nickname 的用户
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"nickname": "gs"}'::jsonb;

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

-- 查询有 python 和 golang 标签的数据
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"tags": ["python", "golang"]}';
 ?column?
----------
 t

?操作符、?|操作符和?&操作符

-- 查询有 avatar 属性的用户
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb ? 'avatar';
-- 查询有 avatar 属性 并且avatar 数据不为空的数据
select '{"nickname": "gs", "avatar": null, "tags": ["python", "golang", "db"]}'::jsonb->>'avatar' is not null;

-- 查询 有 avatar 或 tags 的数据
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?| array['avatar', 'tags'];
 ?column?
----------
 t

-- 查询 既有 avatar 又有 tags 的用户
select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?& array['avatar', 'tags'];
 ?column?
----------
 f

 -- 查询 tags 中包含 python 标签的数据
 select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'tags' ? 'python';
 ?column?
----------
 t

json 和 jsonb 的操作符列表

json 和 jsonb 操作符

额外的 jsonb 操作符

新增或者修改

原始数据结构 {"accountClear": {"clearType": "Clear", "csmReason": "钱不对", "addToBlackList": {"blackCorporate": true, "blackEnterprise": true}, "applyReviewReason": "钱不对"}}

1. 修改 clearType 的参数
   update apply set "data" = jsonb_set("data"::jsonb, '{accountClear, clearType}', '"Clear"', true) where "type" = 'AccountClear';

2. 移除 accountClear
   update apply set "data"= "data"- 'accountClear' where id='4cb0568d-d571-4256-8aea-7eee9a6dca1c';

3. 移除 data 中 accountClear 下的 clearType
   update apply set "data"= "data" #- '{accountClear,clearType}' where id='4cb0568d-d571-4256-8aea-7eee9a6dca1c';

jsonb_set(target         jsonb,  // 需要修改的数据
          path           text[], // 数据路径
          new_value      jsonb,  // 新数据
          create_missing boolean default true)

如果 create_missing 是 true （缺省是 true），并且 path 指定的路径在 target 中不存在，那么 target 将包含 path 指定部分， new_value 替换部分， 或者 new_value 添加部分。

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 索引模块最初也是为了支持全文搜索而开发的。

jsonb 的默认 GIN 操作符类支持使用顶层键存在运算符?、?&以及?| 操作符和路径/值存在运算符@>的查询。

-- 创建默认索引
CREATE INDEX idxgin ON api USING GIN (jdoc);

非默认的 GIN 操作符类 jsonb_path_ops 只支持索引@>操作符。

-- 创建指定路径的索引
CREATE INDEX idxgin ON api USING GIN (jdoc, ('a'->'b'));

eg:

EXPLAIN SELECT * from apply where "data"->'accountClear'->'clearType' ? 'LogOff'

上述 sql 会使用索引：

EXPLAIN SELECT * from apply where "data"->'accountClear'->'clearType' = '"LogOff"'