hillpig的个人博客分享 http://blog.sciencenet.cn/u/hillpig 畅想ing,思考ing,前行ing Email:bluevaley@gmail.com

博文

组合使用PostgreSQL的ARRAY和JSONB数据类型(PG JSON系列3)

已有 7088 次阅读 2016-10-14 13:09 |个人分类:postgresql|系统分类:科研笔记| postgresql, array, jsonb

上篇博客 演示了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查询计划树分析的在线工具
收藏 IP: 61.135.169.*| 热度|

0

该博文允许注册用户评论 请点击登录 评论 (0 个评论)

数据加载中...

Archiver|手机版|科学网 ( 京ICP备07017567号-12 )

GMT+8, 2024-3-29 16:40

Powered by ScienceNet.cn

Copyright © 2007- 中国科学报社

返回顶部