PostgreSQL Jsonb类型示例

创建表

1
2
3
4
5
6
7
8
9
10
11
12
create table person
(
id bigint not null constraint person_pk primary key,
type varchar,
name varchar(20),
pocket jsonb
);

comment on table person is '人的抽象类';
comment on column person.id is '主键ID';
comment on column person.name is '名字';
comment on column person.type is 'pocket类型';

搜索

1
select * from person;

添加数据

1
2
3
4
5
6
7
insert into person (id, name, pocket)
VALUES (1, 'a', '{ "coinAmount": 10 }'),
(2, 'b', '{"chalkColor": "red"}');

insert into person (id, name, pocket)
VALUES (3, 'c', '{"level": 2, "content": {"name": "hello", "price": 1} }'),
(4, 'd', '{"level": 20, "content": {"name": "world", "price": 3} }');
id name pocket
1 a {“coinAmount”: 10}
2 b {“chalkColor”: “red”}

将jsonb中的字段当做列查询

1
2
3
4
select id,
name,
pocket -> 'chalkColor' as color
from person;
id name pocket
1 a
2 b “red”

通过jsonb中第一级属性值搜索

1
2
3
select * from person where pocket -> 'coinAmount' = '10';
select * from person where pocket @> '{"coinAmount": 10}';
select * from person where pocket ->> 'coinAmount' is not null;

通过jsonb中的第二级属性搜索

1
2
select * from person where pocket -> 'content' ->> 'name' = 'hello';
select * from person where pocket @> '{"content":{"name": "hello"}}';

在array的属性中搜索, friends属性(array)中包含1和4的记录

1
select * from person where pocket @> '{"friends": [1,4]}';

属性In操作 属性level(不是数组)的值是2或者20的记录

1
select * from person where pocket ->> 'level' IN ('2', '20');

选取jsonb属性值

1
select pocket -> 'coinAmount' from person;

将字符串转为JSONB

1
SELECT '{"id": 1, "name": "hello"}'::jsonb;

遍历jsonb

1
SELECT jsonb_each('{"id": 1, "name": "hello"}'::jsonb);

打印json的key

1
SELECT jsonb_object_keys('{"id": 1, "name": "hello"}'::jsonb);

提取json key值

1
SELECT jsonb_extract_path('{"id": 1, "name": "hello"}'::jsonb, 'id');

格式化jsonb

1
SELECT jsonb_pretty('{"id": 1, "name": "hello"}'::jsonb);

更新jsonb (注意不是只更新level的属性值)

1
update person set pocket='{"level": 24}' where id=3;

update or insert

1
update person set pocket = pocket || '{"level": 21, "content":{"name":"world"}}' where id = 9;

移除属性 删除 ‘content’属性

1
update person set pocket = pocket - 'content';