Postgres中JSON和JSONB之间的区别[重复]

时间：2021-11-13 16:55:20

This question already has an answer here:

这个问题在这里已有答案:

What's difference between JSON and JSONB data type in PosgresSQL?

PosgresSQL中JSON和JSONB数据类型有什么区别?

  1. When should be used specific one?

    2. 什么时候应该使用具体的?

  2. What's benefits or disadvantages with respect to other?

    3. 对其他人有什么好处或坏处?

2 个解决方案

#1

6  

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很可能是您在寻找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

#2

12  

json is 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. jsonb on 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, jsonb supports 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 json only 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, text it is also a perfectly valid option, especially if you are not interested in the validation that json does (e.g. because you trust the source of the data).

一般情况下,只有当我知道我不会进行任何JSON操作或偶尔执行它们时,才使用json。对于所有其他情况,我使用jsonb。请注意,对于前一种情况,文本它也是一个完全有效的选项,特别是如果您对json所做的验证不感兴趣(例如,因为您信任数据源)。

#1

6  

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很可能是您在寻找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

#2

12  

json is 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. jsonb on 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, jsonb supports 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 json only 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, text it is also a perfectly valid option, especially if you are not interested in the validation that json does (e.g. because you trust the source of the data).

一般情况下,只有当我知道我不会进行任何JSON操作或偶尔执行它们时,才使用json。对于所有其他情况,我使用jsonb。请注意,对于前一种情况,文本它也是一个完全有效的选项,特别是如果您对json所做的验证不感兴趣(例如,因为您信任数据源)。

标签：

相关文章