Postgres中jsonb类型

Jsonb类型

PostgresSql支持两种JSON数据类型：json和jsonb。json类型以文本形式存储json对象，输入的文本是什么样的，存储的文本就是什么样的。每次处理时需要解析和分析文本格式的json，另外还存储了不必要的空白字符和重复键。jsonb类型将文本格式的json对象转换为二进制格式，并删除了不需要的空格及重复键。如果在输入中指定了重复的健，只有最后一个值会被保留。

最重要的一点：jsonb支持索引

通常情况下，选择用jsonb就可以，除非有特殊需求，比如对键值的顺序有要求。

操作

创建数据库并插入几条数据：

CREATE TABLE book (
  id SERIAL PRIMARY KEY,
  client TEXT NOT NULL,
  data JSONb NOT NULL
);

INSERT INTO book(client, data) values 
('Joe', '{ "title": "Siddhartha", "author": { "first_name": "Herman", "last_name": "Hesse"} }'),
('Jenny', '{ "title": "Dharma Bums", "author": { "first_name": "Jack", "last_name": "Kerouac"} }'),
('Jenny', '{ "title": "100 años de soledad", "author": { "first_name": "Gabo", "last_name": "Marquéz"} }');

SELECT * FROM book;

查询

->

select client, data->'title' as title, data->'author' as author from book;

结果如下：

client	title	author
Joe	"Siddhartha"	{"first_name": "Herman", "last_name": "Hesse"}
Jenny	"Dharma Bums"	{"first_name": "Jack", "last_name": "Kerouac"}
Jenny	"100 años de soledad"	{"first_name": "Gabo", "last_name": "Marquéz"}

->>

select client, data->>'title' as title, data->'author'->>'first_name' as author from book;

结果如下：

client	title	author
Joe	Siddhartha	Herman
Jenny	Dharma Bums	Jack
Jenny	100 años de soledad	Gabo

从 -> 和 ->> 两种操作返回的结果可以看出，前者返回json对象，后者返回文本值。这两个操作对json字段同样适用。

嵌套过滤

select client, data->>'title' as title from book where data->'author'->>'last_name' = 'Kerouac';

返回结果：

client	title
Jenny	Dharma Bums

其他操作

json和jsonb都支持

jsonb支持

举例查询：

select * from book where data @> '{"title": "Dharma Bums","author": {"last_name": "Kerouac","first_name": "Jack"}}'::jsonb;

结果如下：

id	client	data
2	Jenny	{"title":"Dharma Bums","author":{"last_name":"Kerouac","first_name":"Jack"}}

更新

update book set data = jsonb_set(data, '{author, first_name}', '"Hermany"') where id = 1;

再来查询更新后的结果：

id	client	data
1	Joe	{"first_name": "Hermany", "last_name": "Hesse"}

使用JPA持久化jsonb字段

添加JSONBinary库：

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.2.2</version>
</dependency>

假设现在有一个Person的entity，其中有一个preference对象字段，需要按jsonb格式存储到数据库。需要在Person类和preference字段上使用jsonb的注解：

@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Person {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    private String name;
 
    @Column(columnDefinition = "jsonb")
    @Type(type = "jsonb")
    private Preference preference;
}

然后正常使用save函数就能就行保存和更新了。