PostgreSQL merge json的正确姿势

背景

json merge是业务常用的功能，例如网络爬虫，更新合并新爬到的内容。

PostgreSQL 9.5 对JSON的类型进行了非常大的功能增强，例如支持合并，按KEY删除，更新KEY VALUE等。

https://www.postgresql.org/docs/9.5/static/functions-json.html

以合并为例

以右边的值为准，支持嵌套值的合并。

postgres=# select jsonb '{"k1":"v1","k2":"v2","k3":{"k1":"v3","k2":"v3","k3":"v3"}}' || jsonb '{"k1":"v1","k2":"v2","k3":{"k1":"v3","k2":"v3","k3":"v4"}}';  
                               ?column?                                 
----------------------------------------------------------------------  
 {"k1": "v1", "k2": "v2", "k3": {"k1": "v3", "k2": "v3", "k3": "v4"}}  
(1 row)  
  
postgres=# select jsonb '{"k1":"v1","k2":"v2","k3":{"k1":"v3","k2":"v3","k3":"v3"}}' || jsonb '{"k0":"v0","k1":"v1","k2":"v10000","k3":{"k1":"v4","k2":"v3","k3":"v4","k4":{"k4":"v4"}}}' ;  
                                                 ?column?                                                   
----------------------------------------------------------------------------------------------------------  
 {"k0": "v0", "k1": "v1", "k2": "v10000", "k3": {"k1": "v4", "k2": "v3", "k3": "v4", "k4": {"k4": "v4"}}}  
(1 row)  
  
postgres=# select jsonb '{"k1":"v1","k2":"v2","k3":{"k1":"v3","k2":"v3","k3":"v3"}}' || jsonb '{"k0":"v0","k2":"v10000","k3":{"k1":"v4","k2":"v3","k3":"v4","k4":{"k4":"v4"}}}' ;  
                                                 ?column?                                                   
----------------------------------------------------------------------------------------------------------  
 {"k0": "v0", "k1": "v1", "k2": "v10000", "k3": {"k1": "v4", "k2": "v3", "k3": "v4", "k4": {"k4": "v4"}}}  
(1 row)  

如果你用的是PostgreSQL9.5 以前的版本，使用jsonbx这个插件也能扩展JSON的功能.

http://pgxn.org/search?q=jsonbx&in=extensions

List of implemented functions  
---------------------------------  
  
* jsonb_indent  
* jsonb_concat  
* jsonb_delete(jsonb, text)  
* jsonb_delete_idx(jsonb, int)  
* jsonb_delete_path(jsonb, text[])  
* jsonb_set(jsonb, text[], jsonb, boolean)  
  
List of implemented operators  
---------------------------------  
  
* concatenation operator (||)  
* delete key operator (jsonb - text)  
* delete key by index operator (jsonb - int)  
* delete key by path operator (jsonb - text[])  

另外需要提醒一下，因为json合并大多数情况下是会导致合并后的JSON更大的，所以使用数据库存储时，即使不是多版本的数据库也可能造成行迁移，导致索引的变化。

PS:

阿里云RDS PG 9.4的用户，如果要使用以上扩展的jsonb功能，在对应实例的对应数据库中创建这个插件即可。

create extension jsonbx;   

如果创建这个扩展报 插件不存在的错误，说明您的PG小版本可能较低，需要先在阿里云RDS控制台升级一下对应实例的版本，然后再创建jsonbx插件。

digoal’s 大量PostgreSQL文章入口