Storing permission or configuration data.Storing user tracking data that needs to be updated over time.Some examples of where you could adopt jsonb include: In PostgreSQL, the jsonb data type is better than its counterpart in pretty much every other situation.

This is especially true if you already know that your log data comes in valid JSON format. Note that in this case, text is also a viable option. This is the most popular scenario where using the json field type is convenient.

You need to preserve the original JSON data indentation and formatīasically, json is perfect for storing logs.

You do not expect to perform complex queries on your JSON data.

You have to perform a lot of INSERT operations.The json data type works better than jsonb when: Let's try to understand when you should prefer json over jsonb and vice versa with some real-world scenarios. This can lead to significant performance advantages when parsing and querying JSON data. Also, jsonb supports many more functions and operators than json. However, the jsonb binary format is significantly faster to process because it does not involve reparsing. This makes the `INSERT` operation a bit slower compared to json due to conversion overhead. On the other hand, jsonb stores data in a decomposed binary format. So, if you need to be careful about JSON formatting, the json PostgreSQL data type can be useful. This is because json preserves the indentation of the input data. So, each function and operation has to reparse the entire field at each execution.Īt the same time, this can also represent an advantage over jsonb. Specifically, the json data type stores an exact copy of the input text. This makes jsonb inherently more efficient than json. The main difference between json and jsonb lies in the way they store data behind the scene. Although there are specific cases where json is better than jsonb, as you are about to learn. Specifically, you can see jsonb as a kind of successor to json. Apart from this, they do not have many other things in common. Conceptually, this is the main aspect the two data types share. You can add a json column to a table with the following query:Īs stated in the official PostgreSQL documentation, the json and jsonb data types basically accept the same sets of values as input. Also, Postgres enforces that the data stored in these fields is valid JSON. This means that Postgres preserves the original key orders, whitespace, and even duplicate keys in objects. Note that json stores JSON data in a raw format. But these operations are rather slow and are not optimized for large JSON data. You can perform some basic JSON operations, such as extracting the value associated with an object key. In other words, the json data type does not offer many useful features. This is because the PostgreSQL json data type is not much more than a simple text field. Yet, the introduction of the json data type was not a revolution. This represented a turning point because it allowed Postgres to start becoming a direct competitor to NoSQL technologies. PostgreSQL introduced the json data type with Postgres 9.2. Keep reading and by the end of the article, you will be able to answer all these questions! JSON Data Type Now, you may be wondering why PostgreSQL has two JSON data types, what are the main differences between them, and when you should use json vs.

Jsonb functions postgres