postgresql Postgres 中 JSON 和 JSONB 的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39637370/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between JSON and JSONB in Postgres
提问by Somnath Muluk
What's difference between JSON and JSONB data type in PosgresSQL?
PosgresSQL 中的 JSON 和 JSONB 数据类型有什么区别？
- When should be used specific one?
- What's benefits or disadvantages with respect to other?
- 具体什么时候应该用？
- 相对于其他人有什么好处或坏处？
采纳答案by Piotr R
This is explain: https://www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb/
这是解释：https: //www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb/
In most cases JSONB is likely what you want when looking for a NoSQL, schema-less, datatype. Hstore and JSON can have their place as well but it's less common. More broadly, JSONB isn't always a fit in every data model. Where you can normalize there are benefits, but if you do have a schema that has a large number of optional columns (such as with event data) or the schema differs based on tenant id then JSONB can be a great fit. In general you want:
JSONB - In most cases JSON - If you're just processing logs, don't often need to query, and use as more of an audit trail hstore - Can work fine for text based key-value looks, but in general JSONB can still work great here
在大多数情况下，JSONB 可能是您在寻找 NoSQL、无模式数据类型时想要的。Hstore 和 JSON 也可以占有一席之地，但不太常见。更广泛地说，JSONB 并不总是适合每个数据模型。可以规范化的地方有很多好处，但是如果您确实有一个包含大量可选列（例如事件数据）的架构，或者架构因租户 ID 而异，那么 JSONB 可能非常适合。一般来说，你想要：
JSONB - In most cases JSON - If you're just processing logs, don't often need to query, and use as more of an audit trail hstore - Can work fine for text based key-value looks, but in general JSONB can still work great here
回答by redneb
jsonis basically a blob that stores JSON data in raw format, preserving even insignificant things such as whitespace, the order of keys in objects, or even duplicate keys in objects. It does offer the ability to do some basic JSON operations such as extracting the value associated with some key in an object, albeit it is slow at that since it has to parse the JSON blob every time. It also validates every value to check that it is valid JSON. jsonbon the other hand stores JSON data in a custom format that is optimized for certain operations such as extracting the value associated with some key in an object (i.e. it will not reparse JSON, it will not search linearly). Additionally, jsonbsupports more operations, just as concatenation of objects or setting a value deep inside an object.
json基本上是一个以原始格式存储 JSON 数据的 blob，甚至保留无关紧要的东西，例如空格、对象中键的顺序，甚至对象中的重复键。它确实提供了执行一些基本 JSON 操作的能力，例如提取与对象中某个键关联的值，尽管它很慢，因为它每次都必须解析 JSON blob。它还验证每个值以检查它是否是有效的 JSON。jsonb另一方面，以自定义格式存储 JSON 数据，该格式针对某些操作进行了优化，例如提取与对象中某个键关联的值（即它不会重新解析 JSON，它不会线性搜索）。此外，jsonb支持更多操作，就像对象的串联或在对象内部设置值一样。
In general, I use jsononly if I know that I will not do any JSON operations or only do them occasionally. For all other cases I use jsonb. Note that for the former case, textit is also a perfectly valid option, especially if you are not interested in the validation that jsondoes (e.g. because you trust the source of the data).
一般来说，我json只在我知道我不会做任何 JSON 操作或只是偶尔做的时候才使用。对于所有其他情况，我使用jsonb. 请注意，对于前一种情况，text它也是一个完全有效的选项，特别是如果您对json所做的验证不感兴趣（例如，因为您信任数据的来源）。
