背景环境
作为一种简单易用的非结构化数据，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)
复制代码
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
