PostgreSQL9.5:JSONB 数据类型: 支持元素修改,新增,删除

9.4 版本中 JSONB 数据类型可以通过函数和操作符获取元素值,但不能修改其元素值,很多有 JSON 需求场景的朋友都非常期待这一功能,好在 9.5 版支持 JSONB 元素修改,可以说 JSONB 数据类型在功能上获得了较大提升。 JSONB 元素修改有两种方法,详见以下。

通过 || 操作符

通过 jsonb || jsonb (concatenate / overwrite) 操作符可以覆盖元素值,例如。

样例 jsonb 数据

1
2
3
4
postgres=# select '{"name":"francs","age":"31"}'::jsonb;  
jsonb
---------------------------------
{"age": "31", "name": "francs"}

修改 age 元素值

1
2
3
4
postgres=# select '{"name":"francs","age":"31"}'::jsonb || '{"age":"32"}'::jsonb;  
?column?
---------------------------------
{"age": "32", "name": "francs"}

通过 jsonb_set() 函数

9.5 版本支持 jsonb_set() 函数修改元素值。

jsonb_set 函数语法

1
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

jsonb_set 函数使用
修改 age 元素值为 32

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
postgres=# select jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{age}','"32"'::jsonb,false);  
jsonb_set
---------------------------------
{"age": "32", "name": "francs"}
create_missing 值为 true:如果元素值不存在,则添加
postgres=# select jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{sex}','"male"'::jsonb,true);
jsonb_set
------------------------------------------------
{"age": "31", "sex": "male", "name": "francs"}
(1 row)

create_missing 值为 false:如果元素值不存在,不添加
postgres=# select jsonb_set('{"name":"francs","age":"31"}'::jsonb,'{sex}','"male"'::jsonb,false);
jsonb_set
---------------------------------
{"age": "31", "name": "francs"}
(1 row)

仔细看下面这个操作的区别

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# SELECT '{"name": "Jane", "contact": {"fax": "0000","phone": "01234 567890", "mobile": "07890 123456"}}'::jsonb || '{"contact": {"fax": "01987 654321"}}'::jsonb;  
?column?
------------------------------------------------------
{"name": "Jane", "contact": {"fax": "01987 654321"}}
(1 row)


postgres=# SELECT jsonb_set('{"name": "Jane", "contact": {"fax": "0000","phone": "01234 567890", "mobile": "07890 123456"}}'::jsonb,'{contact,fax}', '"1111"'::jsonb);
jsonb_set
-------------------------------------------------------------------------------------------------
{"name": "Jane", "contact": {"fax": "1111", "phone": "01234 567890", "mobile": "07890 123456"}}
(1 row)

备注: 如果是嵌套元素,|| 操作会从上层替换整个嵌套元素值。

Jsonb: 添加元素

|| 操作符同时也支持连接 jsonb 数据类型 ,例如,连接 sex 元素值

1
2
3
4
postgres=# select '{"name":"francs","age":"31"}'::jsonb || '{"sex":"male"}'::jsonb;  
?column?
------------------------------------------------
{"age": "31", "sex": "male", "name": "francs"}

Jsonb: 删除元素

删除元素值也有两种方法,一种是通过操作符 - ,另一种通过指定路径删除。

通过操作符删除元素

1
2
3
4
5
6
7
8
postgres=# SELECT '{"name": "James", "email": "james@localhost"}'::jsonb - 'email';  
?column?
-------------------
{"name": "James"}
postgres=# SELECT '["red","green","blue"]'::jsonb - 0;
?column?
-------------------
["green", "blue"]

指定路径删除元素

也可以指定路径删除元素,适合 jsonb 数据有嵌套元素并且内容较多的场景,语法如下:

1
jsonb #- text[] / int (remove key / array element in path)

示例如下:

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# SELECT '{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb #- '{contact,fax}'::text[];  
?column?
---------------------------------------------------------
{"name": "James", "contact": {"phone": "01234 567890"}}
(1 row)


postgres=# SELECT '{"name": "James", "aliases": ["Jamie","The Jamester","J Man"]}'::jsonb #- '{aliases,1}'::text[];
?column?
--------------------------------------------------
{"name": "James", "aliases": ["Jamie", "J Man"]}
(1 row)

jsonb_pretty 函数

jsonb_pretty 函数用来格式化 jsonb 数据类型输出,如下。

1
2
3
4
5
6
7
8
9
10
11
postgres=# SELECT jsonb_pretty('{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb);  
jsonb_pretty
---------------------------------
{ +
"name": "James", +
"contact": { +
"fax": "01987 543210", +
"phone": "01234 567890"+
} +
}
(1 row)

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%