PostgreSQL处理JSON入门

栏目: 数据库 · PostgreSQL · 发布时间: 5年前

内容简介：作为一种简单易用的非结构化数据，JSON格式的应用场景非常广泛。在当前的大数据环境下，处理非结构化数据的需求越来越频繁，我们是不是必须用MongoDB这一类NoSQL的解决方案？强大的PostgreSQL数据库，在RDBMS的基础上提供了对JSON的完善支持，不需要MongoDB也可以玩转JSON。PostgreSQL-9.2中引入了对JSON类型的支持，经过几个大版本的进化，目前对JSON数字类型的支持已经比较完善。在PG中对JSON格式信息的CRUD操作，针对具体的节点建立索引，这些都可以很容易的实现。

作为一种简单易用的非结构化数据，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)
复制代码

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

云计算安全与隐私

Tim Mather、Subra Kumaraswamy、Shahed Latif / 刘戈舟、杨泽明、刘宝旭 / 机械工业出版社华章公司 / 2011-6 / 65.00元

《云计算安全与隐私》可以使你明白当把数据交付给云计算时你所面临的风险，以及为了保障虚拟基础设施和网络应用程序的安全可以采取的行动。本书是由信息安全界知名专家所著，作者在书中给出许多中肯的忠告和建议。本书的读者对象包括：IT职员、信息安全和隐私方面的从业人士、业务经理、服务提供商，以及投资机构等。阅读本书你会了解直到现在还严重匮乏的云计算安全方面的详尽信息。《云计算安全与隐私》主要内容包括：......一起来看看《云计算安全与隐私》这本书的介绍吧!

码农工具