博文
组合使用PostgreSQL的ARRAY和JSONB数据类型（PG JSON系列3）
|||
上篇博客 演示了JSON/JSONB如何做增删改查（PG JSON系列2），这回演示一下Array和JSONB如何组合来使用。我们的问题是：
设计一个典型场景，来演示对Array和JSONB很好的组合来使用？
先上两张图，以展示我们的应用场景：
这是“看照片，认校友”的H5页面，用于分享在微信朋友圈，大家也可以用手机打开这个链接看看：
http://www.yingziedu.com/facelink/publish/index_city.html?activityid=18
二维码扫一扫打开如下：
若用PC浏览器打开的时候，按F12，然后仿真一下手机，左划一下就会出现第二张认照片的页面。
我们在设计数据库的时候，希望：
一次活动包含多张照片，每张照片下面列出照片中所有人的个人信息（姓名/手机号码等），并可以按行来修改
我们可以这么来设计数据库，如下图：
SQL代码如下：
-- Table: TPActivity -- 照片识别活动表（把照片识别看作一次活动，一次活动可能同时识别多张照片） DROP TABLE IF EXISTS TPActivity CASCADE; CREATE TABLE TPActivity ( id bigint DEFAULT nextval('jsontest_uuid_seq') PRIMARY KEY,-- 活动id,全局唯一性，用于外键关联 title character varying(128) NOT NULL,-- 活动名称，如“计算所喊你回家了” pics bigint ARRAY -- 照片id数组 )WITH ( OIDS=FALSE ); -- Table: TPIC -- 照片表 DROP TABLE IF EXISTS TPIC CASCADE; CREATE TABLE TPIC ( id bigint DEFAULT nextval('jsontest_uuid_seq') PRIMARY KEY,-- 活动id,全局唯一性，用于外键关联 picUrl character varying(256) DEFAULT NULL,-- 照片 persons jsonb NOT NULL -- 照片里的人名、电话等，格式为：[{"position":"前1排左1","name":"张晓陆","phone":"18910180100","city":"济南","organization":"中科院计算所","attendanceYear":"2012","graduationYear":"2016"},{"position":"前1排左2","name":"张晓陆","phone":"18910180100","organization":"中科院计算所","attendanceYear":"2012","graduationYear":"2016"}] )WITH ( OIDS=FALSE ); |
测试数据：
-- Table: TPActivity -- 照片识别活动表（把照片识别看作一次活动，一次活动可能同时识别多张照片） insert into TPActivity values(18,'28级4班的你,还记得他/她吗？','{109, 110}'); -- Table: TPIC -- 照片表 insert into TPIC values(109,'/img/userupload/XZZX-90-1.2.png', '[{"position":"前排左1","name":"","phone":"","city":"","organization":"","attendanceYear":"","graduationYear":""}, {"position":"前排左2","name":"","phone":"","city":"","organization":"","attendanceYear":"","graduationYear":""} ]'); insert into TPIC values(110,'/img/userupload/XZZX-90-2.2.png', '[{"position":"前排左1","name":"","phone":"","city":"","organization":"","attendanceYear":"","graduationYear":""}, {"position":"前排左2","name":"","phone":"","city":"","organization":"","attendanceYear":"","graduationYear":""}, {"position":"前排左3","name":"","phone":"","city":"","organization":"","attendanceYear":"","graduationYear":""} ]'); |
其中TPActivity中的pics设计成数组，TPIC中的persons 设计成jsonb。
这么设计的好处是把数据库设计简化、清爽了许多，坏处是不再使用外键。所以这种设计，需要对应用场景做一个权衡，就我们这个“看照片、认校友”的应用中，对数组的操作只有insert之后的访问，不再需要update，故我们认为这种设计最高效。
然后我们可以通过如下SQL语句一下取出来所有的和活动相关的图片：
WITH allPicsInOneActivity AS (select a.title,b.id,b.picurl,b.persons from TPActivity a, TPIC b where a.id =18 and b.id = any ( a.pics) ) select jsonb_pretty(to_jsonb(row_to_json(b.*))) from (SELECT array_to_json(array(select row_to_json(allPicsInOneActivity.*) from allPicsInOneActivity),false) as activityallpics) b |
返回结果为：
| { "activityallpics": [ { "id": 109, "title": "28级4班的你,还记得他/她吗？", "picurl": "/img/userupload/XZZX-90-1.2.png", "persons": [ { "city": "", "name": "", "phone": "", "position": "前排左1", "organization": "", "attendanceYear": "", "graduationYear": "" }, { "city": "", "name": "", "phone": "", "position": "前排左2", "organization": "", "attendanceYear": "", "graduationYear": "" }, { "city": "", "name": "", "phone": "", "position": "前排左3", "organization": "", "attendanceYear": "", "graduationYear": "" } ] }, { "id": 110, "title": "28级4班的你,还记得他/她吗？", "picurl": "/img/userupload/XZZX-90-2.2.png", "persons": [ { "city": "", "name": "", "phone": "", "position": "前排左1", "organization": "", "attendanceYear": "", "graduationYear": "" }, { "city": "", "name": "", "phone": "", "position": "前排左2", "organization": "", "attendanceYear": "", "graduationYear": "" }, { "city": "", "name": "", "phone": "", "position": "前排左3", "organization": "", "attendanceYear": "", "graduationYear": "" } ] } ] } |
是不是感觉很简洁？
总结：在有些外键不是需要做很多检查操作的情况下，数据库设计中组合使用ARRAY和JSONB可以变得更简洁。
码字码了半天，手指头都麻了，如果觉得对您还有用的话，赞助一下辛苦费吧：
https://blog.sciencenet.cn/blog-419883-1008644.html
上一篇：PG 9.6.0版本里jsonb_set()有个bug，截至到2016.10.12
下一篇：收藏一个PG查询计划树分析的在线工具
