PG学习第二篇：JASON数据类型的使用

PostgreSQL的JASON数据类型，反模式化的数据类型，可谓一种神器型的数据类型。
创建测试表和索引：

-- testone 为不带索引的表
CREATE TABLE testone(
  id bigserial primary key,
  content jsonb,
  createtime timestamp default clock_timestamp()
);

-- testtwo 为带索引的表
CREATE TABLE testtwo(
  id bigserial primary key,
  content jsonb,
  createtime timestamp default clock_timestamp()
);
create index idx_content on testtwo using gin(content);

-- 插入测试数据到testone
insert into testone(content) values('{"IP": "59.41.94.211", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "index_leave", "_nocache": "8921403114218", "properties": {"$os": "windows", "url": "file:///D:/Documents/test.html", "$lib": "js", "flag": 1, "$model": "pc", "$browser": "chrome", "stayTime": 120.337, "$os_version": "6.1", "$lib_version": "1.5.11", "$is_first_day": false, "$screen_width": 1366, "$is_first_time": false, "$screen_height": 768, "$browser_version": "45"}, "distinct_id": "159021615dbbe-03f6a67ec-4349052c-1049088-159021615dc113"}');
insert into testone(content) values('{"IP": "59.41.94.211", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "index_visit", "_nocache": "5807199904229", "properties": {"$os": "windows", "url": "file:///D:/Documents/test.html", "$lib": "js", "flag": 1, "title": "", "$model": "pc", "userId": "", "$browser": "chrome", "referUrl": "", "$os_version": "6.1", "$lib_version": "1.5.11", "$is_first_day": false, "$screen_width": 1366, "$is_first_time": true, "$screen_height": 768, "$browser_version": "45"}, "distinct_id": "1590217ec2a3e-080973861-4349052c-1049088-1590217ec2b256"}');
insert into testone(content) values('{"IP": "59.41.94.211", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "buy", "_nocache": "9141590343788", "properties": {"$os": "windows", "url": "file:///D:/Documents/test.html", "$lib": "js", "flag": 1, "$model": "pc", "userId": "", "$browser": "chrome", "ShopName": "云徙科技", "ProductId": "ayd7qI53IHJj2", "buttonName": "", "$os_version": "6.1", "ProductName": 200, "orderNumber": "gaomeshi", "$lib_version": "1.5.11", "$is_first_day": false, "$screen_width": 1366, "$is_first_time": false, "$screen_height": 768, "$browser_version": "45", "ProductPaymentAmount": 20000}, "distinct_id": "1590217ec2a3e-080973861-4349052c-1049088-1590217ec2b256"}');
insert into testone(content) values('{"ip": "59.41.94.211", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "index_leave", "_nocache": "2629897492006", "properties": {"$os": "windows", "url": "file:///D:/Documents/test.html", "$lib": "js", "flag": 1, "$model": "pc", "$browser": "chrome", "stayTime": 96.507, "$os_version": "6.1", "$lib_version": "1.5.11", "$is_first_day": false, "$screen_width": 1366, "$is_first_time": false, "$screen_height": 768, "$browser_version": "45"}, "distinct_id": "1590217ec2a3e-080973861-4349052c-1049088-1590217ec2b256"}');
insert into testone(content) values('{"ip": "59.41.94.211", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "index_visit", "_nocache": "7716627330519", "properties": {"$os": "windows", "url": "file:///D:/Documents/test.html", "$lib": "js", "flag": 1, "title": "", "$model": "pc", "userId": "", "$browser": "chrome", "referUrl": "", "$os_version": "6.1", "$lib_version": "1.5.11", "$is_first_day": false, "$screen_width": 1366, "$is_first_time": true, "$screen_height": 768, "$browser_version": "45"}, "distinct_id": "1590219658f8e-06d20e397-4349052c-1049088-1590219659042c"}');
insert into testone(content) values('{"ip": "59.41.94.211", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "buy", "_nocache": "8540458322968", "properties": {"$os": "windows", "url": "file:///D:/Documents/test.html", "$lib": "js", "flag": 1, "$model": "pc", "userId": "", "$browser": "chrome", "ShopName": "云徙科技", "ProductId": "ayd7qI53IHJj2", "buttonName": "", "$os_version": "6.1", "ProductName": 200, "orderNumber": "gaomeshi", "$lib_version": "1.5.11", "$is_first_day": false, "$screen_width": 1366, "$is_first_time": false, "$screen_height": 768, "$browser_version": "45", "ProductPaymentAmount": 20000}, "distinct_id": "1590219658f8e-06d20e397-4349052c-1049088-1590219659042c"}');
insert into testone(content) values('{"ip": "59.41.95.30", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "index_visit", "_nocache": "6597653267167", "properties": {"$os": "windows", "url": "file:///C:/Users/LWF/Desktop/log/test.html", "$lib": "js", "flag": 1, "$model": "pc", "userId": "", "$browser": "firefox", "referUrl": "", "$os_version": "6.1", "$lib_version": "1.5.11", "$is_first_day": true, "$screen_width": 1920, "$is_first_time": true, "$screen_height": 1080, "$browser_version": "50"}, "distinct_id": "159021aae7a5d7-06255323c1a83c8-45514130-2073600-159021aae7b5be"}');
insert into testone(content) values('{"ip": "59.41.95.30", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "index_visit", "_nocache": "9668076469369", "properties": {"$os": "windows", "url": "http://localhost:9091/#/Register", "$lib": "js", "flag": 1, "$model": "pc", "userId": "", "$browser": "chrome", "referUrl": "", "$os_version": "6.1", "$lib_version": "1.5.11", "$is_first_day": true, "$screen_width": 1920, "$is_first_time": false, "$screen_height": 1080, "$browser_version": "52"}, "distinct_id": "159014bd91155a-06913d8400dc2a-574e6e46-250125-159014bd912389"}');
insert into testone(content) values('{"ip": "59.41.95.30", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "index_visit", "_nocache": "9478349705127", "properties": {"$os": "iPhone OS", "url": "http://localhost:9091/#/Register", "$lib": "js", "flag": 1, "$model": "iphone", "userId": "", "$browser": "safari", "referUrl": "", "$os_version": "9.1", "$lib_version": "1.5.11", "$is_first_day": true, "$screen_width": 375, "$is_first_time": false, "$screen_height": 667, "$browser_version": "9"}, "distinct_id": "159014bd91155a-06913d8400dc2a-574e6e46-250125-159014bd912389"}');
insert into testone(content) values('{"ip": "59.41.95.30", "lib": {"$lib": "js", "$lib_method": "code", "$lib_version": "1.5.11"}, "type": "track", "event": "index_visit", "_nocache": "4449175972282", "properties": {"$os": "iPhone OS", "url": "http://localhost:9091/#/Register", "$lib": "js", "flag": 1, "$model": "iphone", "userId": "", "$browser": "safari", "referUrl": "", "$os_version": "9.1", "$lib_version": "1.5.11", "$is_first_day": true, "$screen_width": 375, "$is_first_time": false, "$screen_height": 667, "$browser_version": "9"}, "distinct_id": "159014bd91155a-06913d8400dc2a-574e6e46-250125-159014bd912389"}');

复制testone表的数据到文件：

copy testone to '/tmp/testdata.bak'

复制上一步备份的数据到testtwo表：

copy testtwo from '/tmp/testdata.bak'

使用下面的语句测试JASON类型的使用，添加explain可查看查询执行计划：

select content->'distinct_id' from testtwo;
select content->>'distinct_id' from testtwo;
select content->'lib'->'$lib' from testtwo;
select content->'lib'->>'$lib' from testtwo;

select content from testone where content @> '{"IP":"59.41.94.211"}';
select content from testtwo where content @> '{"IP":"59.41.94.211"}';
select content from testtwo where content::text like '%"IP": "59.41.94.211"%';

本例中用到的命令：

\l 查看有哪些数据库
\c [dbname] 切换到[dbname]数据库下
\?	查看pg中支持的命令
\d  查看整体表信息
\d [tablename] 查看具体表结构信息
\d+ [tablename] 查看具体表详细结构信息
\d [sequence_id] 查看sequence详细信息
\di [indexname] 查看索引详细信息