PostgreSQL JSONB 使用入门

json 类型

说明

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

1. -- 简单标量/基本值

2. -- 基本值可以是数字、带引号的字符串、true、false或者null

3. SELECT '5'::json;

4.  

5. -- 有零个或者更多元素的数组（元素不需要为同一类型）

6. SELECT '[1, 2, "foo", null]'::json;

7.  

8. -- 包含键值对的对象

9. -- 注意对象键必须总是带引号的字符串

10. SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

11.  

12. -- 数组和对象可以被任意嵌套

13. SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

14.  

15. -- "->" 通过键获得 JSON 对象域 结果为json对象

16. select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' as nickname;

17. nickname

18. -------------

19. "goodspeed"

20.  

21. -- "->>" 通过键获得 JSON 对象域 结果为text

22. select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' as nickname;

23. nickname

24. -----------

25. goodspeed

26.  

27. -- "->" 通过键获得 JSON 对象域 结果为json对象

28. select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' as nickname;

29. nickname

30. -------------

31. "goodspeed"

32.  

33. -- "->>" 通过键获得 JSON 对象域 结果为text

34. select '{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' as nickname;

35. nickname

36. -----------

37. goodspeed

38. 复制代码

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

1. SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;

2. json

3. -------------------------------------------------

4. {"bar": "baz", "balance": 7.77, "active":false}

5.  

6. -- jsonb 不会保留语义上的细节，key 的顺序也和原始数据不一致

7. SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;

8. jsonb

9. --------------------------------------------------

10. {"bar": "baz", "active": false, "balance": 7.77}

11. 复制代码

json 查询语法

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

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

常用的比较操作符

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

包含和存在

json 数据查询（适用于jsonb）

json和jsonb 操作符

 

 

 

-> 和 ->> 操作符

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

1. -- nickname 为 gs 的用户 这里使用 ->> 查出的数据为text，所以匹配项也应该是text

2. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->>'nickname' = 'gs';

3. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';

4.  

5. -- 使用 -> 查询，会抛出错误，这里无论匹配项是text类型的 'gs' 还是 json 类型的 '"gs"'::json都会抛出异常，json 类型不支持 等号（=）操作符

6. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' = '"gs"';

7. ERROR: operator does not exist: json = unknown

8. -- json 类型不支持 "=" 操作符

9. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json->'nickname' = '"gs"'::json;

10. ERROR: operator does not exist: json = json

11.  

12. -- jsonb 格式是可以查询成功的，这里使用 -> 查出的数据为json 对象，所以匹配项也应该是json 对象

13. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';

14. 复制代码

#> 和 #>> 操作符

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

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

2. tag

3. ----------

4. "python"

5.  

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

7. tag

8. --------

9. python

10.  

11. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>'{tags,0}' = '"python"';

12. ?column?

13. ----------

14. t

15. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb#>>'{tags,0}' = 'python';

16. ?column?

17. ----------

18. t

19.  

20. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>>'{tags,0}' = 'python';

21. ?column?

22. ----------

23. t

24. -- 会抛出错误，这里无论匹配项是text类型的 'python' 还是 json 类型的 '"python"'::json都会抛出异常，json 类型不支持 等号（=）操作符

25. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' = '"python"';

26. ERROR: operator does not exist: json = unknown

27. 复制代码

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

** 额外的jsonb操作符**

 

 

 

@>操作符

1. -- nickname 为 nickname 的用户

2. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"nickname": "gs"}'::jsonb;

3.  

4. -- 等同于以下查询

5. -- 这里使用 -> 查出的数据为json 对象，所以匹配项也应该是json 对象

6. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';

7. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->>'nickname' = 'gs';

8.  

9. -- 查询有 python 和 golang 标签的数据

10. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"tags": ["python", "golang"]}';

11. ?column?

12. ----------

13. t

14. 复制代码

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

1. -- 查询有 avatar 属性的用户

2. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb ? 'avatar';

3. -- 查询有 avatar 属性 并且avatar 数据不为空的数据

4. select '{"nickname": "gs", "avatar": null, "tags": ["python", "golang", "db"]}'::jsonb->>'avatar' is not null;

5.  

6. -- 查询 有 avatar 或 tags 的数据

7. select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?| array['avatar', 'tags'];

8. ?column?

9. ----------

10. t

11.  

12. -- 查询 既有 avatar 又有 tags 的用户

13. select '{"nickname": "gs", "tags": ["python", "golang", "db"]}'::jsonb ?& array['avatar', 'tags'];

14. ?column?

15. ----------

16. f

17.  

18. -- 查询 tags 中包含 python 标签的数据

19. select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'tags' ? 'python';

20. ?column?

21. ----------

22. t

23. 复制代码

json 更新

1. -- 更新 account content 字段（覆盖式更新）

2. update account set content = jsonb_set(content, '{}', '{"nickname": "gs", "tags": ["python", "golang", "db"]}', false);

3.  

4. -- 修改nickanme为nickanme 的用户标签

5. update account set content = jsonb_set(content, '{tags}', '["test", "心理"]', true) where content @> '{"nickname": "nickname"}'::jsonb;

6.  

7. update account set content = jsonb_set(content, '{tags}', '["test", "心理", "医疗"]', true) where content @> '{"nickname": "nickname"}'::jsonb;

8.  

9. -- 更新account content字段中 weixin_mp 的值（如果没有会创建）

10.  

11. update account set content = jsonb_set(content, '{weixin_mp}', '"weixin_mp5522bd28-ed4d-11e8-949c-7200014964f0"', true) where id='5522bd28-ed4d-11e8-949c-7200014964f0';

12.  

13. -- 更新account 去除content 中weixin 字段（如果没有weixin 字段也不会抛出异常）

14. update account set content= content - 'weixin' where id='5522bd28-ed4d-11e8-949c-7200014964f0';

15. 复制代码

json 函数

jsonb_pretty

作为缩进JSON文本返回from_json。

1. select jsonb_pretty('[{"f1":1,"f2":null},2,null,3]');

2. jsonb_pretty

3. --------------------

4. [ +

5. { +

6. "f1": 1, +

7. "f2": null+

8. }, +

9. 2, +

10. null, +

11. 3 +

12. ]

13. (1 row)

14. 复制代码

jsonb_set

jsonb_set() 函数参数如下：

1. jsonb_set(target jsonb, // 需要修改的数据

2. path text[], // 数据路径

3. new_value jsonb, // 新数据

4. create_missing boolean default true)

5. 复制代码

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

1. -- target 结构

2. select jsonb_pretty('[{"f1":1,"f2":null},2]');

3. jsonb_pretty

4. --------------------

5. [ +

6. { +

7. "f1": 1, +

8. "f2": null+

9. }, +

10. 2 +

11. ]

12.  

13. -- 更新 target 第0 个元素 key 为 f1 的值，如果f1 不存在 忽略

14. select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false);

15. jsonb_set

16. ---------------------------------------------

17. [{"f1": [2, 3, 4], "f2": null}, 2, null, 3]

18.  

19. -- 更新 target 第0 个元素 key 为 f3 的值，如果f3 不存在 创建

20. select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]');

21. jsonb_set

22. ---------------------------------------------

23. [{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]

24.  

25. -- 更新 target 第0 个元素 key 为 f3 的值，如果f3 不存在 忽略

26. select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]', false);

27. jsonb_set

28. ---------------------------------------------

29. [{"f1": 1, "f2": null}, 2]

30. 复制代码

详细的json 函数和操作符可以参考文档：JSON 函数和操作符

jsonb 性能分析

我们使用下面的例子来说明一下json 的查询性能

表结构

1. -- account 表 id 使用uuid 类型，需要先添加uuid-ossp模块。

2. CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

3.  

4. -- create table

5. create table account (id UUID NOT NULL PRIMARY KEY default uuid_generate_v1(), content jsonb, created_at timestamptz DEFAULT CURRENT_TIMESTAMP, updated_at timestamptz DEFAULT CURRENT_TIMESTAMP);

6. json=> \d account

7. Table "public.account"

8. Column | Type | Collation | Nullable | Default

9. --------------+--------------------------+-----------+----------+--------------------

10. id | uuid | | not null |uuid_generate_v1()

11. content | jsonb | | |

12. created_at | timestamp with time zone | | | CURRENT_TIMESTAMP

13. updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP

14. Indexes:

15. "account_pkey" PRIMARY KEY, btree (id)

16. 复制代码

一个好的实践是把 created_at和 updated_at 也放入jsonb 字段，这里只是示例

content 数据结构为：

1. content = {

2. "nickname": {"type": "string"},

3. "avatar": {"type": "string"},

4. "weixin": {"type": "string"},

5. "tags": {"type": "array", "items": {"type": "string"}},

6. }

7. 复制代码

准备数据

批量插入数据

1. -- 插入100w条有 nickname avatar tags 为["python", "golang", "c"]的数据

2. insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*20000000) || '", "avatar": "avatar_url", "tags": ["python", "golang", "c"]}')::jsonb from (select * from generate_series(1,100000)) as tmp;

3.  

4. -- 插入100w条有 nickname tags 为["python", "golang"]的数据

5. insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*2000000) || '", "tags": ["python", "golang"]}')::jsonb from (select * from generate_series(1,1000000)) as tmp;

6.  

7. -- 插入100w条有 nickname tags 为["python"]的数据

8. insert into account select uuid_generate_v1(), ('{"nickname": "nn-' || round(random()*2000000) || '", "tags": ["python"]}')::jsonb from (select * from generate_series(1,1000000)) as tmp;

9. 复制代码

测试查询

• EXPLAIN：显示PostgreSQL计划程序为提供的语句生成的执行计划。
• ANALYZE：收集有关数据库中表的内容的统计信息。

1.  

2. --content 中有avatar key 的数据条数 count(*) 查询不是一个好的测试语句，就算是有索引，也只能起到过滤的作用，如果结果集比较大，查询速度还是会很慢

3. explain analyze select count(*) from account where content::jsonb ? 'avatar';

4. QUERY PLAN

5. ----------------------------------------------------------------------------------------

6. Finalize Aggregate (cost=29280.40..29280.41 rows=1 width=8) (actual time=170.366..170.366 rows=1 loops=1)

7. -> Gather (cost=29280.19..29280.40 rows=2 width=8) (actual time=170.119..174.451 rows=3 loops=1)

8. Workers Planned: 2

9. Workers Launched: 2

10. -> Partial Aggregate (cost=28280.19..28280.20 rows=1 width=8) (actual time=166.034..166.034 rows=1 loops=3)

11. -> Parallel Seq Scan on account (cost=0.00..28278.83 rows=542 width=0) (actual time=0.022..161.937 rows=33333 loops=3)

12. Filter: (content ? 'avatar'::text)

13. Rows Removed by Filter: 400000

14. Planning Time: 0.048 ms

15. Execution Time: 174.486 ms

16.  
17.  

18. -- content 中没有avatar key 的数据条数

19. explain analyze select count(*) from account where content::jsonb ? 'avatar' = false;

20. QUERY PLAN

21. ----------------------------------------------------------------------------------------

22. Finalize Aggregate (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.770..207.770 rows=1 loops=1)

23. -> Gather (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.681..212.357 rows=3 loops=1)

24. Workers Planned: 2

25. Workers Launched: 2

26. -> Partial Aggregate (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.565..203.565 rows=1 loops=3)

27. -> Parallel Seq Scan on account (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.050..163.629 rows=400000 loops=3)

28. Filter: (NOT (content ? 'avatar'::text))

29. Rows Removed by Filter: 33333

30. Planning Time: 0.050 ms

31. Execution Time: 212.393 ms

32. 复制代码

1. --查询content 中nickname 为nn-194318的数据

2. explain analyze select * from account where content@>'{"nickname": "nn-194318"}';

3. QUERY PLAN

4. ----------------------------------------------------------------------------------------

5. Gather (cost=1000.00..29408.83 rows=1300 width=100) (actual time=0.159..206.990 rows=1 loops=1)

6. Workers Planned: 2

7. Workers Launched: 2

8. -> Parallel Seq Scan on account (cost=0.00..28278.83 rows=542 width=100) (actual time=130.867..198.081 rows=0 loops=3)

9. Filter: (content @> '{"nickname": "nn-194318"}'::jsonb)

10. Rows Removed by Filter: 433333

11. Planning Time: 0.047 ms

12. Execution Time: 207.007 ms

13.  

14. -- 对应的查询id 为 'b5b3ed06-7d35-11e9-b3ea-00909e9dab1d' 的数据

15. explain analyze select * from account where id='b5b3ed06-7d35-11e9-b3ea-00909e9dab1d';

16. QUERY PLAN

17. ----------------------------------------------------------------------------------------

18. Index Scan using account_pkey on account (cost=0.43..8.45 rows=1 width=100) (actual time=0.912..0.914 rows=1 loops=1)

19. Index Cond: (id = 'b5b3ed06-7d35-11e9-b3ea-00909e9dab1d'::uuid)

20. Planning Time: 0.348 ms

21. Execution Time: 0.931 ms

22. 复制代码

通过结果可以看到 使用 jsonb 查询和使用主键查询速度差异巨大，通过看查询分析记录可以看到，这两个语句最大的差别在于使用主键的查询用到了索引，而content nickname 的查询没有索引可以使用。 接下来测试一下使用索引时的查询速度。

索引

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 操作符类支持使用顶层键存在运算符?、?&以及?| 操作符和路径/值存在运算符@>的查询。

1. -- 创建默认索引

2. CREATE INDEX idxgin ON api USING GIN (jdoc);

3. 复制代码

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

1. -- 创建指定路径的索引

2. CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

3. -- create index ix_account_content_nickname_gin on account using gin (content, (content->'nickname'));

4. -- create index ix_account_content_tags_gin on account using gin (content, (content->'nickname'));

5. -- create index ix_account_content_tags_gin on account using gin ((content->'tags'));

6. 复制代码

多索引支持

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

查询优化

创建默认索引

1. -- 创建简单索引

2. create index ix_account_content on account USING GIN (content);

3. 复制代码

现在下面这样的查询就能使用该索引：

1. -- content 中有avatar key 的数据条数

2. explain analyze select count(*) from account where content::jsonb ? 'avatar';

3. QUERY PLAN

4. ------------------------------------------------------------------------------------------------------------------------------------------

5. Aggregate (cost=4180.49..4180.50 rows=1 width=8) (actual time=43.462..43.462 rows=1 loops=1)

6. -> Bitmap Heap Scan on account (cost=30.07..4177.24 rows=1300 width=0) (actual time=8.362..36.048 rows=100000 loops=1)

7. Recheck Cond: (content ? 'avatar'::text)

8. Heap Blocks: exact=2032

9. -> Bitmap Index Scan on ix_account_content (cost=0.00..29.75 rows=1300 width=0) (actual time=8.125..8.125 rows=100000 loops=1)

10. Index Cond: (content ? 'avatar'::text)

11. Planning Time: 0.078 ms

12. Execution Time: 43.503 ms

13. 复制代码

和之前没有添加索引时速度提升了3倍。

1. -- 查询content 中nickname 为nn-194318的数据

2. explain analyze select * from account where content@>'{"nickname": "nn-194318"}';

3. QUERY PLAN

4. ----------------------------------------------------------------------------------------

5. Bitmap Heap Scan on account (cost=46.08..4193.24 rows=1300 width=100) (actual time=0.097..0.097 rows=1 loops=1)

6. Recheck Cond: (content @> '{"nickname": "nn-194318"}'::jsonb)

7. Heap Blocks: exact=1

8. -> Bitmap Index Scan on ix_account_content (cost=0.00..45.75 rows=1300 width=0) (actual time=0.091..0.091 rows=1 loops=1)

9. Index Cond: (content @> '{"nickname": "nn-194318"}'::jsonb)

10. Planning Time: 0.075 ms

11. Execution Time: 0.132 ms

12. 复制代码

这个查询效率提升更明显，竟然比使用主键还要高效。

但是下面这种查询并不能使用索引：

1. -- 查询content 中不存在 avatar key 的数据条数

2. explain analyze select count(*) from account where content::jsonb ? 'avatar' = false;

3. QUERY PLAN

4. ----------------------------------------------------------------------------------------

5. Finalize Aggregate (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.641..207.641 rows=1 loops=1)

6. -> Gather (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.510..211.062 rows=3 loops=1)

7. Workers Planned: 2

8. Workers Launched: 2

9. -> Partial Aggregate (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.739..203.739 rows=1 loops=3)

10. -> Parallel Seq Scan on account (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.024..163.444 rows=400000 loops=3)

11. Filter: (NOT (content ? 'avatar'::text))

12. Rows Removed by Filter: 33333

13. Planning Time: 0.068 ms

14. Execution Time: 211.097 ms

15. 复制代码

该索引也不能被用于下面这样的查询，因为尽管操作符? 是可索引的，但它不能直接被应用于被索引列content：

1. explain analyze select count(1) from account where content -> 'tags' ? 'c';

2. QUERY PLAN

3. ----------------------------------------------------------------------------------------

4. Finalize Aggregate (cost=30634.57..30634.58 rows=1 width=8) (actual time=184.864..184.864 rows=1 loops=1)

5. -> Gather (cost=30634.35..30634.56 rows=2 width=8) (actual time=184.754..189.652 rows=3 loops=1)

6. Workers Planned: 2

7. Workers Launched: 2

8. -> Partial Aggregate (cost=29634.35..29634.36 rows=1 width=8) (actual time=180.755..180.755 rows=1 loops=3)

9. -> Parallel Seq Scan on account (cost=0.00..29633.00 rows=542 width=0) (actual time=0.022..177.051 rows=33333 loops=3)

10. Filter: ((content -> 'tags'::text) ? 'c'::text)

11. Rows Removed by Filter: 400000

12. Planning Time: 0.074 ms

13. Execution Time: 189.716 ms

14. 复制代码

使用表达式索引

1. -- 创建路径索引

2. create index ix_account_content_tags on account USING GIN ((content->'tags'));

3. 复制代码

1. -- 测试查询性能

2. explain analyze select count(1) from account where content -> 'tags' ? 'c';

3. QUERY PLAN

4. ----------------------------------------------------------------------------------------

5. Aggregate (cost=4631.74..4631.75 rows=1 width=8) (actual time=49.274..49.275 rows=1 loops=1)

6. -> Bitmap Heap Scan on account (cost=478.07..4628.49 rows=1300 width=0) (actual time=8.655..42.074 rows=100000 loops=1)

7. Recheck Cond: ((content -> 'tags'::text) ? 'c'::text)

8. Heap Blocks: exact=2032

9. -> Bitmap Index Scan on ix_account_content_tags (cost=0.00..477.75 rows=1300 width=0) (actual time=8.417..8.417 rows=100000 loops=1)

10. Index Cond: ((content -> 'tags'::text) ? 'c'::text)

11. Planning Time: 0.216 ms

12. Execution Time: 49.309 ms

13. 复制代码

现在，WHERE 子句content -> 'tags' ? 'c' 将被识别为可索引操作符?在索引表达式content -> 'tags' 上的应用。

也可以利用包含查询的方式，例如：

1. -- 查寻 "tags" 包含数组元素 "c" 的数据的个数

2. select count(1) from account where content @> '{"tags": ["c"]}';

3. 复制代码

content 列上的简单 GIN 索引（默认索引）就能支持索引查询。 但是索引将会存储content列中每一个键 和值的拷贝，表达式索引只存储tags 键下找到的数据。

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

总结

• PG 有两种 JSON 数据类型：json 和 jsonb，jsonb 性能优于json，且jsonb 支持索引。
• jsonb 写入时会处理写入数据，写入相对较慢，json会保留原始数据（包括无用的空格）
• jsonb 查询优化时一个好的方式是添加GIN 索引
  • 简单索引和路径索引相比更灵活，但是占用空间多
  • 路径索引比简单索引更高效，占用空间更小

参考链接

• RFC 7159 The JavaScript Object Notation (JSON) Data Interchange Format
• PostgreSQL 文档： JSON 类型
• JSON 函数和操作符
• How do I modify fields inside the new PostgreSQL JSON datatype?
• PostgreSQL 9种索引的原理和应用场景
• PostgreSQL GIN索引实现原理
• PostgreSQL internals: JSONB type and its indexes
• 倒排索引
•  

最后，感谢女朋友支持和包容，比❤️

也可以在公号输入以下关键字获取历史文章：公号&小程序 | 设计模式 | 并发&协程