postgresql jsonb

准备测试数据

DROP TABLE IF EXISTS "tbl";
create table tbl(id int, js jsonb);  
create index idx_tbl_1 on tbl using gin (js jsonb_path_ops);  
create index idx_tbl_2 on tbl using gin (js); 
ALTER TABLE "public"."tbl" ADD CONSTRAINT "tbl_pkey" PRIMARY KEY ("id");

INSERT INTO "tbl" VALUES (1, '{"sex": "男", "city": "贡嘎", "ogran": "贡嘎中心医院", "office": "内科"}');
INSERT INTO "tbl" VALUES (2, '{"sex": "男", "city": "玉树", "ogran": "玉树中心医院", "office": "内科"}');
INSERT INTO "tbl" VALUES (3, '{"sex": "男", "city": "西藏", "ogran": "西藏中心医院", "office": "内科"}');
INSERT INTO "tbl" VALUES (4, '{"sex": "男", "city": "西藏", "ogran": "人民中心医院", "office": "内科"}');
INSERT INTO "tbl" VALUES (5, '{"sex": "男", "city": "西藏", "ogran": "林芝中心医院", "office": "内科"}');
INSERT INTO "tbl" ("id", "js") VALUES (6, '{"items": {"qty": 1, "product": "Toy Car"}, "customer": "Josh William"}');

explain select * from tbl where js  @> '{"city": "西藏"}';

PostGreSql支持json类型的字段和jsonb类型的字段

json和jsonb的区别

• json类型把输入的数据原封不动的存放到数据库中。jsonb类型在存放时把JSON解析成二进制格式，读出再解析
• jsonb写入比 json 慢,但检索较 json快
• json存储占内存比jsonb小

一、json类型

1.添加json类型的字段

alter table t_message add params_json json;

2.创建索引

方法一：

create index idx_params_id on t_message using btree (json_extract_path_text(params_json, 'id'));

方法二：

create index idx_params_name on t_message using btree ((params_josn->>'name'));

3.执行

方法一走索引的查询

select * from test where json_extract_path_text( params_json,'id')='123';

方法二走索引的查询

select * from test where params_json->>'name'='123';

二、jsonb类型

josnb字段创建 GIN 索引, GIN 索引有两种模式, 默认模式支持  @>, ?, ?& 和 ?| 的索引查询

• 使用默认的jsonb_ops操作符创建。
• 使用jsonb_path_opsselect * from test where params_json->>'name'='123';

操作符创建。

  两者的区别是：

•   jsonb_ops的GIN索引中，JSONB数据中的每个key和value都是作为一个单独的索引项。
•   jsonb_path_ops只为每一个value创建一个索引项。

    例如，一个项{“foo”:{"bar":"baz"}}，jsonb_ops会分别为“foo”,"bar","baz"创建索引项，共创建了三个。jsonb_path_ops则是把“foo”,"bar","baz"组合成一个hash值作为索引项。因此，通常jsonb_path_ops的索引较小。

 我们这里使用默认模式

1.添加jsonb类型

alter table t_message add params_jsonb jsonb;

  使用默认的jsonb_ops操作符创建索引。

create index idx_gin_params_jsonb on t_message using gin (params_jsonb);

  使用jsonb_path_ops操作符创建索引。

create index idx_gin_params_jsonb on t_message using gin(params_jsonb jsonb_path_ops);

3.执行

不走索引：

select * from t_message where params_jsonb->>'loanId' ='123';

走索引：

select * from t_message where params_jsonb @> '{"loanId": 123}';

走索引：

select * from t_message where params_jsonb->'loanId' ?'123'; 

结果：没走

? 操作没走索引, 但 ? 操作支持索引检索,创建以下索引.

create index idx_gin_params_jsonb_loaId on t_message using gin((params_jsonb -> 'loanId'));