背景环境

作为一种简单易用的非结构化数据，JSON格式的应用场景非常广泛。在当前的大数据环境下，处理非结构化数据的需求越来越频繁，我们是不是必须用MongoDB这一类NoSQL的解决方案？强大的PostgreSQL数据库，在RDBMS的基础上提供了对JSON的完善支持，不需要MongoDB也可以玩转JSON。

PostgreSQL-9.2中引入了对JSON类型的支持，经过几个大版本的进化，目前对JSON数字类型的支持已经比较完善。在PG中对JSON格式信息的CRUD操作，针对具体的节点建立索引，这些都可以很容易的实现。

本次我们测试在PG中使用JSON的常见场景，软件环境如下

CentOS 7 x64

PostgreSQL 11.1

两种数据类型

PG中提供了两种不同的数据类型，分别是JSON和JSONB。顾名思义，JSON是存储字符串的原始格式，而JSONB是二进制编码版本。JSON需要存储包括空格等原始格式，所以在每次查询的时候都会有解析过程。而JSONB查询时不需要实时解析，所以更高效。

简而言之，JSON 为了准确存储，插入快查询慢；JSONB 为了高效查询，插入慢检索快。

如果没有特殊理由，最好使用JSONB类型。

-- 使用 JSONB 字段类型（无特殊需求不要使用JSON类型）
drop table if exists demo.j_waybill;
create table demo.j_waybill (id int primary key, data jsonb);

insert into demo.j_waybill(id, data) values(1,
	'
	{
	    "waybill": 2019000000,
	    "project": "测试项目",
	    "pay_org_name": "ABC制造厂",    
	    "driver": {
	        "name": "张三",
	        "mobile": 13800000000
	    },
	    "line": {
	        "from": {"province":"河北省", "city":"唐山市", "district":"丰润区"},
	        "to": {"province":"四川省", "city":"绵阳市", "district":"市辖区"}
	    },   
	    "payment": {
	        "oil_amount": 1234,
	        "cash_amount": 5678
	    }
	}
	'
);

数据查询

格式化输出

-- jsonb_pretty() 函数，打印更可读的JSON输出
select jsonb_pretty(w.data) from demo.j_waybill w where w.id = 1;
           jsonb_pretty
-----------------------------------
 {                                +
     "line": {                    +
         "to": {                  +
             "city": "绵阳市",    +
             "district": "市辖区",+
             "province": "四川省" +
         },                       +
         "from": {                +
             "city": "唐山市",    +
             "district": "丰润区",+
             "province": "河北省" +
         }                        +
     },                           +
     "driver": {                  +
         "name": "张三",          +
         "mobile": 13800000000    +
     },                           +
     "payment": {                 +
         "oil_amount": 1234,      +
         "cash_amount": 5678      +
     },                           +
     "project": "测试项目",       +
     "waybill": 2019000000,       +
     "pay_org_name": "ABC制造厂"  +
 }
(1 row)

提取对象成员

PG提供了两种类型的查询语法，分别是用于提取顶级成员的 -> ，和提取嵌套成员的#> 语法。如果仅想取出文本内容，使用 ->> 或 #>> 即可。

-- 提取顶级成员， 注意 -> 和 ->> 的区别，后者取出的是文本值
select
     w.data->'waybill' as waybill,
     w.data->'project' as project,
     w.data->>'project' as project_text
 from demo.j_waybill w where w.id = 1;

waybill   |  project   | project_text
------------+------------+--------------
 2019000000 | "测试项目" | 测试项目
(1 row)

-- 指定节点的路径来提取嵌套成员，仍然有 #> 和 #>> 的区别
select 
	w.data#>'{driver}' as driver, 
	w.data#>>'{driver, name}' as driver_name, 
	w.data#>'{driver, mobile}' as mobile 
from demo.j_waybill w where w.id = 1;

                 driver                  | driver_name |   mobile
-----------------------------------------+-------------+-------------
 {"name": "张三", "mobile": 13800000000} | 张三        | 13800000000
(1 row)

条件筛选

PG提供了特殊的存在判断符号？。这种语法和 is not null 是等价的。

-- 判断是否存在指定的顶级key
select count(1) from demo.j_waybill w where w.data ? 'waybill';
 count
-------
     1
(1 row)

-- 上一句的等价语句如下
select count(1) from demo.j_waybill w where w.data->'waybill' is not null ;


-- 判断嵌套中的key是否存在
select count(1) from demo.j_waybill w where w.data->'driver' ? 'mobile';
 count
-------
     1
(1 row)

?| 和 ?& 对 ? 的功能进行扩展，等价于 or 和 and 操作。

-- 多个条件的判断  ?| 表示or， ?& 表示and
select count(1) from demo.j_waybill w where w.data->'driver' ?| '{"mobile", "addr"}';

除了检查key的存在之外，还可以用 @> 符号检查key:value。

-- ? 仅用来检查 key 存在，那么 @>  可以检查子串的功能
select count(1) from demo.j_waybill w where w.data @> '{"waybill":2019000000, "project":"测试项目"}';
 count
-------
     1
(1 row)

-- 上一句的等价语句如下
-- PS：数字参数要用to_jsonb()，字符串要用 ->> 提取
select count(1) from demo.j_waybill w 
	where w.data->'waybill' = to_jsonb(2019000000) 
	and w.data->>'project' = '测试项目' ;
	
-- 也可以使用类型转换
select count(1) from demo.j_waybill w 
	where (w.data->'waybill')::numeric = 2019000000 
	and w.data->>'project' = '测试项目' ;

数据更新

新增/合并

-- 合并操作符 || 用来增加新的节点，演示如下
select 
	jsonb_pretty(w.data#>'{line}' || '{"new_line":"增加的"}') as new_line,
	jsonb_pretty(w.data || '{"new_key":"增加的"}') as new_key
from demo.j_waybill w where w.id = 1;

           new_line            |              new_key
-------------------------------+-----------------------------------
 {                            +| {                                +
     "to": {                  +|     "line": {                    +
         "city": "绵阳市",    +|         "to": {                  +
         "district": "市辖区",+|             "city": "绵阳市",    +
         "province": "四川省" +|             "district": "市辖区",+
     },                       +|             "province": "四川省" +
     "from": {                +|         },                       +
         "city": "唐山市",    +|         "from": {                +
         "district": "丰润区",+|             "city": "唐山市",    +
         "province": "河北省" +|             "district": "丰润区",+
     },                       +|             "province": "河北省" +
     "new_line": "增加的"     +|         }                        +
 }                             |     },                           +
                               |     "driver": {                  +
                               |         "name": "张三",          +
                               |         "mobile": 13800000000    +
                               |     },                           +
                               |     "new_key": "增加的",         +
                               |     "payment": {                 +
                               |         "oil_amount": 1234,      +
                               |         "cash_amount": 5678      +
                               |     },                           +
                               |     "project": "测试项目",       +
                               |     "waybill": 2019000000,       +
                               |     "pay_org_name": "ABC制造厂"  +
                               | }
(1 row)

-- 操作符可以用在update语法中
update demo.j_waybill 
	set data = data || '{"new_key":"增加的"}' ;

删除

-- 删除整个顶级成员
update demo.j_waybill 
	set data = data-'driver'  ;
	
-- 删除指定路径下的成员	
update demo.j_waybill 
	set data = data#-'{driver, mobile}'  ;	
	
-- 同时删除多个成员	
update demo.j_waybill 
	set data = data#-'{driver, mobile}'#-'{line, to}'  ;

修改

jsonb_set（）就是设计用来更新单一路径节点值。参数含义如下：

第一个就是你要修改的 JSONB 数据类型字段；
第二个是一个文本数组，用来指定修改的路径；
第三个参数是要替换值（可以是 JSON）；
如果给的路径不存在，json_set() 默认会创建他；如果想要禁用这个行为，那就把第四个参数设置成 false；

-- 字符串，要使用双引号
update demo.j_waybill set data = jsonb_set(data, '{"project"}', '"变更的"' );

-- 数字，要使用to_jsonb()
update demo.j_waybill set data = jsonb_set(data, '{"waybill"}', to_jsonb(100) );

-- 新增简单元素
update demo.j_waybill set data = jsonb_set(data, '{"new_simple"}', to_jsonb(999) );

-- 增加复杂元素
update demo.j_waybill set data = jsonb_set(data, '{"new_complex"}', '{"foo":"bar", "foo1": 123}');

索引

PG自带的gin类型索引，可以支持除了范围查询之外的所有JSON操作。我们用一些例子来进行说明。

-- 建立样例表
drop table if exists demo.j_cargo;
create table demo.j_cargo (id int primary key, data jsonb);

insert into demo.j_cargo(id, data)
select v.waybill_id, to_jsonb(v)
from (
	select b.waybill_create_time, c.*
		from dwd_lhb.wb_cargo_info as c, dwd_lhb.wb_base_info as b 
	where c.waybill_id = b.waybill_id 
	limit 100000
) as v
;

默认模式

gin有两种使用模式，默认不带任何参数。创建index如下

-- 支持除范围查询以外的所有查询
drop index if exists idx_jc_non_ops ;
create index idx_jc_non_ops on demo.j_cargo using gin (data);

判断指定KEY是否存在的？操作，如下

-- 查看执行计划确认用到索引
explain select * from demo.j_cargo j where j.data ? 'cargo_name';
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on j_cargo j  (cost=16.77..389.25 rows=100 width=803)
   Recheck Cond: (data ? 'cargo_name'::text)
   ->  Bitmap Index Scan on idx_jc_non_ops  (cost=0.00..16.75 rows=100 width=0)
         Index Cond: (data ? 'cargo_name'::text)
(4 rows)

判断指定Key:Value是否相等的 @> 操作，如下

-- 判断值相等，用到索引
explain select * from demo.j_cargo j where j.data @> '{"cargo_name":"尿素"}' ;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on j_cargo j  (cost=28.77..401.25 rows=100 width=803)
   Recheck Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
   ->  Bitmap Index Scan on idx_jc_non_ops  (cost=0.00..28.75 rows=100 width=0)
         Index Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
(4 rows)

OR操作的值相等判断

-- PS：多个值or操作也用到索引
explain select * from demo.j_cargo j where j.data @> '{"cargo_name":"尿素"}' or j.data @> '{"cargo_name":"白酒"}';
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on j_cargo j  (cost=57.60..775.81 rows=200 width=803)
   Recheck Cond: ((data @> '{"cargo_name": "尿素"}'::jsonb) OR (data @> '{"cargo_name": "白酒"}'::jsonb))
   ->  BitmapOr  (cost=57.60..57.60 rows=200 width=0)
         ->  Bitmap Index Scan on idx_jc_non_ops  (cost=0.00..28.75 rows=100 width=0)
               Index Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
         ->  Bitmap Index Scan on idx_jc_non_ops  (cost=0.00..28.75 rows=100 width=0)
               Index Cond: (data @> '{"cargo_name": "白酒"}'::jsonb)
(7 rows)

jsonb_path_ops 模式

带有jsonb_path_ops的gin索引，效率比默认高。

-- jsonb_path_ops只支持@>操作符，但是效率高
drop index if exists idx_jc_ops ;
create index idx_jc_ops on demo.j_cargo using gin (data jsonb_path_ops);

查看执行计划，确定使用了更高效的索引 idx_jc_ops

explain select * from demo.j_cargo j where j.data @> '{"cargo_name":"尿素"}' ;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on j_cargo j  (cost=16.77..389.25 rows=100 width=803)
   Recheck Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
   ->  Bitmap Index Scan on idx_jc_ops  (cost=0.00..16.75 rows=100 width=0)
         Index Cond: (data @> '{"cargo_name": "尿素"}'::jsonb)
(4 rows)

btree索引 - 数字

因为gin索引不支持范围查询，所以我们把有这种需求的字段提出来建立btree索引。在创建的时候，必须进行显式的类型转换，如下

-- 支持范围查询，把范围查询的类型提取出来，创建btree表达式索引
drop index if exists idx_jc_btree_num ;
create index idx_jc_btree_num on demo.j_cargo ( ((data->>'price')::numeric) );

使用索引的时候也需要执行类型转换，如下

explain select * from demo.j_cargo j where (j.data->>'price')::numeric between 10 and 100;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on j_cargo j  (cost=13.42..1673.22 rows=500 width=803)
   Recheck Cond: ((((data ->> 'price'::text))::numeric >= '10'::numeric) AND (((data ->> 'price'::text))::numeric <= '100'::numeric))
   ->  Bitmap Index Scan on idx_jc_btree_num  (cost=0.00..13.29 rows=500 width=0)
         Index Cond: ((((data ->> 'price'::text))::numeric >= '10'::numeric) AND (((data ->> 'price'::text))::numeric <= '100'::numeric))
(4 rows)

btree索引 - 时间戳

重要：如果直接创建timestamp类型的btree索引，会因为默认的字符串转时间戳函数不满足IMMUTABLE特性而报错，错误如下

-- Timestamp 错误！！！ 因为默认的字符串转时间戳函数不满足immutable
create index idx_jc_btree_ts on demo.j_cargo ( ((data->>'waybill_create_time')::timestamp) );
ERROR:  functions in index expression must be marked IMMUTABLE

正确的做法是，创建一个IMMUTABLE函数进行类型转换，如下

-- 自定义immutable函数处理时间戳
drop function if exists demo.to_timestamp  ;
create or replace function demo.to_timestamp(text) returns timestamp as ?  
  select $1::timestamp;  
? language sql strict immutable;  

--
drop index if exists idx_jc_btree_ts ;
create index idx_jc_btree_ts on demo.j_cargo ( demo.to_timestamp(data->>'waybill_create_time') );

在SQL中也需要使用自定义函数才能用到索引，演示如下

-- 自定义函数用到索引
explain select * from demo.j_cargo j where  demo.to_timestamp(j.data->>'waybill_create_time') between '2015-06-27' and '2015-06-28';
                                                                                                                          QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------
 Bitmap Heap Scan on j_cargo j  (cost=13.42..1918.22 rows=500 width=803)
   Recheck Cond: ((demo.to_timestamp((data ->> 'waybill_create_time'::text)) >= '2015-06-27 00:00:00'::timestamp without time zone) AND (demo.to_timestamp((data ->> 'waybill_create_time'::text)) <= '201
5-06-28 00:00:00'::timestamp without time zone))
   ->  Bitmap Index Scan on idx_jc_btree_ts  (cost=0.00..13.29 rows=500 width=0)
         Index Cond: ((demo.to_timestamp((data ->> 'waybill_create_time'::text)) >= '2015-06-27 00:00:00'::timestamp without time zone) AND (demo.to_timestamp((data ->> 'waybill_create_time'::text)) <=
'2015-06-28 00:00:00'::timestamp without time zone))
(4 rows)

-- 不用自定义函数的时候，使用的是filter操作
explain select * from demo.j_cargo j where (j.data->>'waybill_create_time')::timestamp between '2015-06-27' and '2015-06-28';
                                                                                                                                    QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------
 Gather  (cost=1000.00..13167.00 rows=500 width=803)
   Workers Planned: 2
   ->  Parallel Seq Scan on j_cargo j  (cost=0.00..12117.00 rows=208 width=803)
         Filter: ((((data ->> 'waybill_create_time'::text))::timestamp without time zone >= '2015-06-27 00:00:00'::timestamp without time zone) AND (((data ->> 'waybill_create_time'::text))::timestamp w
ithout time zone <= '2015-06-28 00:00:00'::timestamp without time zone))
(4 rows)