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

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

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

“Postgresql不是已经有一些json的支持了吗？”

是的，在Postgresql 9.4之前的版本也有JSON 数据类型了，你可以这样：

CREATETABLEjustjson(idINTEGER,docJSON)
>INSERTINTOjustjsonVALUES(1,'{
"name":"fred","address":{
"line1":"52TheElms","line2":"Elmstreet","postcode":"ES11ES"
}
}');

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

>SELECT*FROMjustjson;
id|doc
----+---------------------------------
1|{+
|"name":"fred",+
|"address":{+
|"line1":"52TheElms",+
|"line2":"Elmstreet",+
|"postcode":"ES11ES"+
|}+
|}
(1row)

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

selectdoc->>'address'FROMjustjson;
?column?
---------------------------------
{+
"line1":"52TheElms",+
"line2":"Elmstreet",+
"postcode":"ES11ES"+
}
(1row)

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

selectdoc->'address'->>'postcode'FROMjustjson;
?column?
----------
ES11ES
(1row)

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

selectdoc#>>'{address,postcode}'FROMjustjson;
?column?
----------
ES11ES
(1row)

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

"JSONB有什么不同？"

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

>CREATETABLEjustjsonb(idINTEGER,docJSONB)
>INSERTINTOjustjsonbVALUES(1,"postcode":"ES11ES"
}
}');
>SELECT*FROMjustjsonb;
id|doc
----+----------------------------------------------------------------------------------------------------
1|{"name":"fred","address":{"line1":"52TheElms","postcode":"ES11ES"}}
(1row)

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

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

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

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

索引，到处用上索引，你不能在Postgresql对JSON类型创建真正的索引，你可以创建表达式索引（expression indexes），但只限于你想索引的内容，例如：

createindexjustjson_postcodeonjustjson((doc->'address'->>'postcode'));

只有邮编（postcode）索引了，其它都没有索引。

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

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

explainselect*fromjustjsonbwheredoc@>'{"address":{"postcode":"HA36CC"}}';
QUERYPLAN
-----------------------------------------------------------------
SeqScanonjustjsonb(cost=0.00..3171.14rows=100{"address":{"postcode":"HA36CC"}}'::jsonb)
(2rows)

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

>createindexjustjsonb_ginonjustjsonbusinggin(doc);
>explainselect*fromjustjsonbwheredoc@>'{"address":{"postcode":"HA36CC"}}';
QUERYPLAN
-------------------------------------------------------------------------------
BitmapHeapScanonjustjsonb(cost=40.78..367.62rows=100{"address":{"postcode":"HA36CC"}}'::jsonb)
->BitmapIndexScanonjustjsonb_gin(cost=0.00..40.75rows=100{"address":{"postcode":"HA36CC"}}'::jsonb)
(4rows)

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

>createindexjustjsonb_ginonjustjsonbusinggin(docjsonb_path_ops);
>explainselect*fromjustjsonbwheredoc@>'{"address":{"postcode":"HA36CC"}}';
QUERYPLAN
-------------------------------------------------------------------------------
BitmapHeapScanonjustjsonb(cost=16.78..343.62rows=100{"address":{"postcode":"HA36CC"}}'::jsonb)
->BitmapIndexScanonjustjsonb_gin(cost=0.00..16.75rows=100{"address":{"postcode":"HA36CC"}}'::jsonb)
(4rows)

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

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

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

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

参考

Postgresql vs MongoDBhttp://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>