PostgreSQL引入的JSONB解释

本文翻译自：Explanation of JSONB introduced by PostgreSQL

PostgreSQL just introduced JSONB and it's already trending on hacker news . PostgreSQL刚刚推出了JSONB ，它已经在黑客新闻上发展趋势。 It would be great if someone could explain how it's different from Hstore and JSON previously present in PostgreSQL. 如果有人能够解释它与之前在PostgreSQL中出现的Hstore和JSON有何不同，那就太好了。 What are it's advantages and limitations and when should someone consider using it? 它的优点和局限是什么？何时有人考虑使用它？

---

#1楼

参考：https://stackoom.com/question/1x3nQ/PostgreSQL引入的JSONB解释

---

#2楼

Peeyush: Peeyush：

The short answer is: 简短的回答是：

• If you are doing a lot of JSON manipulation inside PostgreSQL, such as sorting, slicing, splicing, etc., you should use JSONB for speed reasons. 如果你在 PostgreSQL中进行了很多JSON操作，比如排序，切片，拼接等，你应该出于速度原因使用JSONB。
• If you need indexed lookups for arbitrary key searches on JSON, then you should use JSONB. 如果您需要在JSON上进行任意键搜索的索引查找，那么您应该使用JSONB。
• If you are doing neither of the above, you should probably use JSON. 如果您不同时使用上述两种方法，则应该使用JSON。
• If you need to preserve key ordering, whitespace, and duplicate keys, you should use JSON. 如果需要保留键排序，空格和重复键，则应使用JSON。

For a longer answer, you'll need to wait for me to do a full "HowTo" writeup closer to the 9.4 release. 要获得更长的答案，您需要等待我在9.4版本附近进行完整的“HowTo”写作。

---

#3楼

• hstore is more of a "wide column" storage type, it is a flat (non-nested) dictionary of key-value pairs, always stored in a reasonably efficient binary format (a hash table, hence the name). hstore更像是一个“宽列”存储类型，它是一个平键（非嵌套）键值对字典，总是存储在一个合理有效的二进制格式（哈希表，因此名称）。
• json stores JSON documents as text, performing validation when the documents are stored, and parsing them on output if needed (ie accessing individual fields); json将JSON文档存储为文本，在存储文档时执行验证，并在需要时在输出上解析它们（即访问单个字段）; it should support the entire JSON spec. 它应该支持整个JSON规范。 Since the entire JSON text is stored, its formatting is preserved. 由于存储了整个JSON文本，因此会保留其格式。
• jsonb takes shortcuts for performance reasons: JSON data is parsed on input and stored in binary format, key orderings in dictionaries are not maintained, and neither are duplicate keys. 出于性能原因， jsonb采用快捷方式：JSON数据在输入时解析并以二进制格式存储，字典中的键排序不会被维护，也不是重复键。 Accessing individual elements in the JSONB field is fast as it doesn't require parsing the JSON text all the time. 访问JSONB字段中的各个元素非常快，因为它不需要始终解析JSON文本。 On output, JSON data is reconstructed and initial formatting is lost. 在输出时，重建JSON数据并丢失初始格式。

IMO, there is no significant reason for not using jsonb once it is available, if you are working with machine-readable data. IMO，如果你正在使用机器可读数据，没有明显的理由不使用jsonb 。

---

#4楼

First, hstore is a contrib module, which only allows you to store key => value pairs, where keys and values can only be text s (however values can be sql NULL s too). 首先， hstore是一个contrib模块，它只允许你存储key => value对，其中键和值只能是text s（但值也可以是sql NULL ）。

Both json & jsonb allows you to store a valid JSON value (defined in its spec ). json和jsonb允许您存储有效的JSON 值 （在其规范中定义）。

F.ex. F.ex. these are valid JSON representations: null , true , [1,false,"string",{"foo":"bar"}] , {"foo":"bar","baz":[null]} - hstore is just a little subset compared to what JSON is capable (but if you only need this subset, it's fine). 这些是有效的JSON表示： null ， true ， [1,false,"string",{"foo":"bar"}] ， {"foo":"bar","baz":[null]} - hstore是与JSON的功能相比只是一个小子集（但如果你只需要这个子集，那就没关系）。

The only difference between json & jsonb is their storage: json和jsonb之间的唯一区别是它们的存储：

• json is stored in its plain text format, while json以纯文本格式存储，而
• jsonb is stored in some binary representation jsonb存储在某种二进制表示中

There are 3 major consequences of this: 这有三个主要后果：

• jsonb usually takes more disk space to store than json (sometimes not) jsonb通常比json需要更多的磁盘空间来存储（有时不会）
• jsonb takes more time to build from its input representation than json jsonb比json需要更多的时间来构建输入表示
• json operations take significantly more time than jsonb (& parsing also needs to be done each time you do some operation at a json typed value) json操作需要显著的时间比jsonb （解析也需要在每一个你做一些工作时间做json类型值）

When jsonb will be available with a stable release, there will be two major use cases, when you can easily select between them: 当jsonb可用于稳定版本时，将有两个主要用例，当您可以轻松地在它们之间进行选择时：

1. If you only work with the JSON representation in your application, PostgreSQL is only used to store & retrieve this representation, you should use json . 如果您只在应用程序中使用JSON表示，PostgreSQL仅用于存储和检索此表示，您应该使用json 。
2. If you do a lot of operations on the JSON value in PostgreSQL, or use indexing on some JSON field, you should use jsonb . 如果您对PostgreSQL中的JSON值执行了大量操作，或者在某些JSON字段上使用索引，则应使用jsonb 。

---

#5楼

As far as I can tell, 据我所知，

• hstore as it currently exists (in Postgresql 9.3) does not allow for nesting other objects and arrays as the values of its key/value pairs. 当前存在的hstore（在Postgresql 9.3中）不允许将其他对象和数组嵌套为其键/值对的值。 however a future hstore patch will allow for nesting. 但是，未来的hstore补丁将允许嵌套。 this patch will not be in the 9.4 release and may not be included any time soon. 此补丁不会在9.4版本中，也可能不会很快包含在内。

• json as it currently exists does allow for nesting, but is text-based, and does not allow for indexing, thus it is "slow" JSON，因为它目前存在确实允许嵌套，但基于文本的，并且不允许索引，因此它是“慢”

• jsonb that will be released with 9.4 will have the current nesting capabilities of json, as well as the GIN/GIST indexing of hstore, so it will be fast 将与9.4一起发布的jsonb将具有json的当前嵌套功能，以及hstore的GIN / GIST索引，因此它将很快

People working on postgresql 9.4 seem to be saying that the new, fast jsonb type will appeal to people who would have chosen to use a noSQL data store like MongoDB, but can now combine a relational database with query-able unstructured data under one roof 在postgresql 9.4上工作的人似乎在说新的快速jsonb类型会吸引那些选择使用像MongoDB这样的noSQL数据存储的人，但现在可以将关系数据库与可查询的非结构化数据结合在一起

http://www.databasesoup.com/2014/02/why-hstore2jsonb-is-most-important.html http://www.databasesoup.com/2014/02/why-hstore2jsonb-is-most-important.html

Benchmarks of postgresql 9.4 jsonb seem to be on par with or in some cases faster than MongoDB postgresql 9.4 jsonb的基准测试似乎与MongoDB相当或在某些情况下比MongoDB更快

http://texture.io/alphabetum/postgresql-incl-hstore-vs-mongodb http://texture.io/alphabetum/postgresql-incl-hstore-vs-mongodb

---

#6楼

I was at the pgopen today benchmarks are way faster than mongodb, I believe it was around 500% faster for selects. 我今天在pgopen基准测试比mongodb更快，我认为选择速度快了约500％。 Pretty much everything was faster at least by at 200% when contrasted with mongodb, than one exception right now is a update which requires completely rewriting the entire json column something mongodb handles better. 与mongodb相比，几乎所有东西都至少快了200％，而现在的一个例外是需要完全重写mongodb处理的整个json列的更新。

The gin indexing on on jsonb sounds amazing. 关于jsonb的杜松子酒索引听起来很神奇。

Also postgres will persist types of jsonb internally and basically match this with types such as numeric, text, boolean etc. postgres还会在内部持久保存jsonb类型，并且基本上与数字，文本，布尔等类型匹配。

Joins will also be possible using jsonb 使用jsonb也可以加入

Add PLv8 for stored procedures and this will basically be a dream come true for node.js developers. 为存储过程添加PLv8，这对于node.js开发人员来说基本上是梦想成真。

Being it's stored as binary jsonb will also strip all whitespace, change the ordering of properties and remove duplicate properties using the last occurance of the property. 将它存储为二进制jsonb也将删除所有空格，更改属性的顺序并使用属性的最后一次出现删除重复属性。

Besides the index when querying against a jsonb column contrasted to a json column postgres doesn't have to actually run the functionality to convert the text to json on every row which will likely save a vast amount of time alone. 除了查询与json列对比的jsonb列时的索引postgres不必实际运行将文本转换为每行的json的功能，这可能会节省大量的时间。