PostgreSQL的数组和JSON字段

PostgreSQL里有两种特殊的字段类型，数组和JSON，这2个字段在处理一些数据类型时会非常方便。不用多建表还能方便的查询其中内容。

数组

数据中的多对多，我们之前一般需要建一个映射表来处理；或者是在某方建一个比较长的字段（例如text类型）然后用某种分隔符（例如逗号）保存数据。

一对多如果在在「一」方保存「多」的信息，也只能类似多对多的处理方式。

有了数组类型，就可以直接建个数组字段保存这些值了，很简单的实现了一对多。

数组字段的查询

例如，我们创建一个表，产品表，每个产品可以有多张图片，图片用数组字段

create table product(
  id      serial primary key,
  name  char(50)  not null,
  imgs   varchar(50)  not null
)

添加一条记录，有3张图p1.jpg, p2.jpg, p3.jpg

insert into product(name, imgs) 
   values('手机', '{"p1.jpg", "p2.jpg", "p3.jpg"}');

查询imgs字段包含 p1.jpg 的记录

select * from product where 'p2.jpg' = ANY(imgs)

更多

PostgreSQL 支持多维数组，而且是各种类型的数组、int[], timestamp[] 等等，不仅仅是字符串数组，多维数组形式例如： varchar(100)[][]

JSON

JSON字段用于保存更复杂的数据类型。

json vs. jsonb

PostgreSQL中有2种JSON类型： json 和 jsonb ，两种类型的区别是：

• json 保存原始值，不会对值进行处理（例如去空格，去重等）。insert/update后 select出来的值的字符串形式是相同的
• jsonb 保存是处理过的值，会对值去空格去重；insert/update后 select出来的值的字符串形式会不同
• jsonb 保存(insert/update)速度会稍慢于json
• 如果使用PostgreSQL 自身的JSON函数查询字段，jsonb会快于json

根据这个特点，如果我们的JSON数据只是在程序中处理，可以选择json类型；如果需要PostgreSQL处理则选择jsonb更佳。

官方文档

Postgresql 的官方文档做得非常好，更多信息看

• https://www.postgresql.org/docs/current/arrays.html
• https://www.postgresql.org/docs/current/datatype-json.html

PostgreSQL还有些其它很有意思的字段，例如：区间类型（日期区间、数值区间等）、复合类型、枚举类型、甚至点线多边形等几何图形等字段类型，而且对于这些类型都有相应的一些辅助运算函数的支持。数据类型