PostgreSQL是不是你的下一个JSON数据库?

Stella981
• 阅读 673

根据Betteridge定律(任何头条的设问句可以用一个词来回答:不是),除非你的JSON数据很少修改,并且查询很多。

最新版的PostgreSQL添加更多对JSON的支持,我们曾经问过PostgreSQL是否可以替换MongoDB作为JSON数据库,答案显而易见,但我们更希望的是,啊哈,这个问题由读者来问了。

“PostgreSQL不是已经有一些json的支持了吗?”

是的,在PostgreSQL 9.4之前的版本也有JSON 数据类型了,你可以这样:

CREATE TABLE justjson ( id INTEGER, doc JSON)
>INSERT INTO justjson VALUES ( 1, '{
    "name":"fred",
    "address":{
        "line1":"52 The Elms",
        "line2":"Elmstreet",
        "postcode":"ES1 1ES"
        }
    }');

保存了JSON的原始文本到数据库,包括空白行和键顺序及重新的键,我们来查看下保存的数据:

>SELECT * FROM justjson;
 id |               doc
----+---------------------------------
  1 | {                              +
    |     "name":"fred",             +
    |     "address":{                +
    |         "line1":"52 The Elms", +
    |         "line2":"Elmstreet",   +
    |         "postcode":"ES1 1ES"   +
    |         }                      +
    |     }
(1 row)

跟保存之前的文本一模一样,但我们仍可以解析出具体的数据出来,PostgreSQL提供了一套JSON的操作方法进行查找,例如,我们只要查出address信息,如果做?

select doc->>'address' FROM justjson;  
            ?column?
---------------------------------
 {                              +
         "line1":"52 The Elms", +
         "line2":"Elmstreet",   +
         "postcode":"ES1 1ES"   +
         }
(1 row)

doc字段的 ->> 操作符是查询JSON对象的某个字段并返回文本,用数字也可以当作数组的索引,但仍返回文本。跟 ->> 类似的还有 -> 操作符,返回不转文本的内容,可以用它来导航搜索JSON对象,如:

select doc->'address'->>'postcode' FROM justjson;  
 ?column?
----------
 ES1 1ES
(1 row)

还有个更简短的写法来指定搜索路径,用 #>> 操作符,如梦:

select doc#>>'{address,postcode}' FROM justjson;  
 ?column?
----------
 ES1 1ES
(1 row)

通过保存完整的JSON数据类型可使其跟源数据完全一样并且不会丢失内容,但为保持完全一致也带来了成本,性能的缺失,而且不能索引...所有,尽管可以很方便的维持一致性和保持JSON文档,但仍有很大的提升空间,所以引入了JSONB。

"JSONB有什么不同?"

JSONB可以将整个JSON文档转有层级的KEY/VALUE数据对,所有的空白字符删除了,重复键只保留最后一次,键也没有排序,而是用HASH来保存了,上面的例子中用JSONB的版本的话,看来起类似这样:

>CREATE TABLE justjsonb ( id INTEGER, doc JSONB)
>INSERT INTO justjsonb VALUES ( 1, '{
    "name":"fred",
    "address":{
        "line1":"52 The Elms",
        "line2":"Elmstreet",
        "postcode":"ES1 1ES"
        }
    }');
>SELECT * FROM justjsonb;
 id |                                                doc
----+----------------------------------------------------------------------------------------------------
  1 | {"name": "fred", "address": {"line1": "52 The Elms", "line2": "Elmstreet", "postcode": "ES1 1ES"}}
(1 row)

可以看到,所有非文本内容都消失了,替换成JSON文档需要的最少格式,这种压缩方式表示当数据插入时会自动格式化,这样可以减少之后访问数据分析处理的工作量。

"PostgreSQL的这种数据有点像HSTORE"

看到键值对,JSONB还真有点像PostgreSQL的HSTORE扩展,它也可以保存键值对,但它是一个扩展,而,JSONB(以及JSON)是在PostgreSQL内核的,HSTORE只有一级层级,但PostgreSQL可以有嵌套的元素,并且,HSTORE只能存字符串,而JSONB还可以存JSON的所数字类型。

“那JSONB到底带给我啥好处呢?”

索引,到处用上索引,你不能在PostgreSQL对JSON类型创建真正的索引,你可以创建表达式索引(expression indexes),但只限于你想索引的内容,例如:

create index justjson_postcode on justjson ((doc->'address'->>'postcode'));

只有邮编(postcode)索引了,其它都没有索引。

而JSONB,支持GIN索引,一种通用返转索引(Generalized Inverted Index),PostgreSQL提供了另外一套索引操作符来支持,包括 @> 包括JSON,<@ 最包含,? 测试字符串是否存在,?| 任意字符串是否存在,?& 所有存大的字符串。

有两类索引可用,默认叫 json_ops,它支持所有操作符(译者:指普通json操作符)和一个只支持&>操作符的jsonb_path_ops索引(译者:指索引操作符),默认索引给JSON中的每个键值都创建了索引,其实 jsonb_path_ops只创建了一个比默认复杂的更高压缩的hash表索引,但默认索引担任更多操作能力同时增加了空间成本。给表添加一些数据,我们再来看看某个邮编,如果我们创建了一个默认的GIN JSON索引然后查询:

explain select * from justjsonb where doc @> '{ "address": { "postcode":"HA36CC" } }';  
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on justjsonb  (cost=0.00..3171.14 rows=100 {"address": {"postcode": "HA36CC"}}'::jsonb)
(2 rows)

可以看出来是顺序扫瞄表,如果我们加个默认的JSON GIN索引后再看看有什么不同?

> create index justjsonb_gin on justjsonb using gin (doc);
> explain select * from justjsonb where doc @> '{ "address": { "postcode":"HA36CC" } }';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on justjsonb  (cost=40.78..367.62 rows=100 {"address": {"postcode": "HA36CC"}}'::jsonb)
   ->  Bitmap Index Scan on justjsonb_gin  (cost=0.00..40.75 rows=100 {"address": {"postcode": "HA36CC"}}'::jsonb)
(4 rows)

搜索性能提升很大,但隐藏了空间的耗费,例中是41%的数据大小,让我们删除索引重复执行jsonb_path_ops GIN索引。

> create index justjsonb_gin on justjsonb using gin (doc jsonb_path_ops);
> explain select * from justjsonb where doc @> '{ "address": { "postcode":"HA36CC" } }';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Bitmap Heap Scan on justjsonb  (cost=16.78..343.62 rows=100 {"address": {"postcode": "HA36CC"}}'::jsonb)
   ->  Bitmap Index Scan on justjsonb_gin  (cost=0.00..16.75 rows=100 {"address": {"postcode": "HA36CC"}}'::jsonb)
(4 rows)

总成本低了点,索引体积小了很多,这是典型的创建索引速度和空间平衡的方法,但比顺序扫瞄性能高很多。

“我应该用它作为我的JSON数据库吗?”

如果你经常更新你的JSON文档,回答是否定的,PostgreSQL最擅长的是存储和攻取JSON文档及他们的字段,但尽管如此你可以取出单个字段,你也不能更新单个字段;实际上你可以,将整个JSON解析出来,添加新的字段再写回,让JSON分析器处理重复,但你很明显不想依赖这个。

如果你的主要数据用关系数据库用得很好,JSON数据只是一群补充(静态数据),那么用PostgreSQL就可以了,而且用JSONB表示和索引能力将更高效。另外,如果你的数据模型是可变内容的集合,那么你可能会寻找一样主流工业级的json文档数据库如MongoDBRethinkDB

参考

PostgreSQL vs MongoDB http://my.oschina.net/Suregogo/blog/358277

Query JSON in PostgreSQL http://schinckel.net/2014/05/25/querying-json-in-postgres/

原文: https://www.compose.io/articles/is-postgresql-your-next-json-database/

<译:朱淦 350050183@qq.com 2015.8.9>

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
3年前
java将前端的json数组字符串转换为列表
记录下在前端通过ajax提交了一个json数组的字符串,在后端如何转换为列表。前端数据转化与请求varcontracts{id:'1',name:'yanggb合同1'},{id:'2',name:'yanggb合同2'},{id:'3',name:'yang
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这