JSON is one of the most popular human-readable data formats. PostgreSQL allows you to store JSON data and perform queries on it thanks to the json and jsonb data types. The introduction of these JSON data types represented a game changing moment for relational databases and opened up many new opportunities.
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 and vice versa. Keep reading and by the end of the article, you will be able to answer all these questions!
JSON Data Type
PostgreSQL introduced the json data type with Postgres 9.2. This represented a turning point because it allowed Postgres to start becoming a direct competitor to NoSQL technologies. Yet, the introduction of the json data type was not a revolution. This is because the PostgreSQL json data type is not much more than a simple text field.
In other words, the json data type does not offer many useful features. You can perform some basic JSON operations, such as extracting the value associated with an object key. But these operations are rather slow and are not optimized for large JSON data.
Note that json stores JSON data in a raw format. This means that Postgres preserves the original key orders, whitespace, and even duplicate keys in objects. Also, Postgres enforces that the data stored in these fields is valid JSON.
You can add a json column to a table with the following query:
1 ALTER TABLE 'users' ADD COLUMN 'log' json;
Then, you can insert data in such a column like so:
1 INSERT INTO 'users'`
2 ("log") VALUES('{ "loggedAt": "2022-04-06 12:09:51.654000 +00:00", "attempts":`
3 [{"number": "1", "status": "failed"}, {"number": "2", "status": "success"}]');`
Image 1 - Performing the two queries above in DbVisualizer
This is pretty much everything you can do with json. Let's now dig into the PostgreSQL json data type.
JSONB Data Type
PostgreSQL added the jsonb data type in Postgres 9.4. This is when the JSON support in PostgreSQL became real. Note that the 'b' at the end of the data type name stands for 'better'. This is because jsonb stores JSON data in a special binary representation. That format is compressed and more efficient than text.
In detail, jsonb is based on an optimized format that enables many new operations. For example, extracting the value associated with an object key becomes lightning fast. Also, jsonb allows you to:
- Set a new key
- Update the value of an existing key
- Set a value in a nested object
- Update the value of a nested key
- Delete a key
- Delete a nested key
- Concatenate JSON objects
- Deal with JSON array
You can have a look at all the built-in jsonb functions offered by PostgreSQL here.
In addition to this, jsonb also has an existence operator. This allows you to test whether a string appears as an object key or array element. You can use it as follows:
1 -- veryfing if the JSON array includes the 'postgresql' element
2 SELECT '["postgresql", "mysql", "oracle"]'::jsonb ? 'postgresql';
When executed, this query would return true.
Plus, jsonb supports GIN (Generalized Inverted Index) indexes. These provide the ability to efficiently search for keys and/or key/value pairs in a large number of jsonb documents.
You can add a jsonb column to an existing table with the query below:
1 ALTER TABLE ´users´ ADD COLUMN ´config´ jsonb;
Then, you can insert data in such a column as below:
1 INSERT INTO ´users´ ("config")
2 VALUES('{"subscriptionLevel": "hero", "points": 450, "achievements": [4, 7, 9], "skins": {"special": true, "hallowing": true, "christmas": false}}');
Image 2 - Running two JSONB queries in DbVisualizer
What Do JSON and JSONB Have in Common?
As stated in the official PostgreSQL documentation, the json and jsonb data types basically accept the same sets of values as input. Conceptually, this is the main aspect the two data types share. Apart from this, they do not have many other things in common. Specifically, you can see jsonb as a kind of successor to json. Although there are specific cases where json is better than jsonb, as you are about to learn.
JSON vs. JSONB: Main differences
The main difference between json and jsonb lies in the way they store data behind the scene. This makes jsonb inherently more efficient than json. Specifically, the json data type stores an exact copy of the input text. So, each function and operation has to reparse the entire field at each execution.
At the same time, this can also represent an advantage over jsonb. This is because json preserves the indentation of the input data. So, if you need to be careful about JSON formatting, the json PostgreSQL data type can be useful.
On the other hand, jsonb stores data in a decomposed binary format. This makes the INSERT operation a bit slower compared to json due to conversion overhead. However, the jsonb binary format is significantly faster to process because it does not involve reparsing. Also, jsonb supports many more functions and operators than json.
Moreover, jsonb supports indexing. This can lead to significant performance advantages when parsing and querying JSON data.
What to Choose Between JSON and JSONB
Let's try to understand when you should prefer json over jsonb and vice versa with some real-world scenarios.
JSON
The json data type works better than jsonb when:
- You have to perform a lot of INSERT operations
- You do not expect to perform complex queries on your JSON data
- You need to preserve the original JSON data indentation and format
Basically, json is perfect for storing logs. This is the most popular scenario where using the json field type is convenient. Note that in this case, text is also a viable option. This is especially true if you already know that your log data comes in valid JSON format.
JSONB
In PostgreSQL, the jsonb data type is better than its counterpart in pretty much every other situation. Some examples of where you could adopt jsonb include:
- Storing user tracking data that needs to be updated over time
- Storing permission or configuration data
- Storing highly nested data whose structure may change over time
In general, you should use jsonb whenever you have to deal with dynamic JSON data or when you need to perform advanced queries.
Conclusion
PostgreSQL supports JSON data through the json and jsonb data types. In most scenarios, jsonb is the perfect solution if you are looking for a NoSQL-like data type. On the other hand, there are some specific situations where json can have its place. Here, you learn what json and jsonb are, how they work, and when to adopt one or the other.
Keep in mind that DbVisualizer fully support both json and jsonb and many other PostgreSQL features. It is the highest rated PostgreSQL client..
Download DbVisualizer for free and find out what it has to offer!
Thanks for reading! We hope that you found this article helpful.
About the author
Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.
Top comments (0)