JSON vs. JSONB in PostgreSQL: A Complete Comparison


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.

Tools used in the tutorial
Tool Description Link

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 2 ALTER TABLE 'users' ADD COLUMN 'log' json;

Then, you can insert data in such a column like so:

1 2 INSERT INTO 'users' 3 ("log") VALUES('{ "loggedAt": "2022-04-06 12:09:51.654000 +00:00", "attempts": 4 [{"number": "1", "status": "failed"}, {"number": "2", "status": "success"}]');
Working with JSON data type in DbVisualizer.
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 2 -- veryfing if the JSON array includes the 'postgresql' element 3 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 2 INSERT INTO ´users´ ("config") 3 VALUES('{"subscriptionLevel": "hero", "points": 450, "achievements": [4, 7, 9], "skins": {"special": true, "hallowing": true, "christmas": false}}');
Running two JSONB queries in DbVisualizer
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.


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.


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.

Dbvis download link img


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, such as many other PostgreSQL features. DbVisualizer is a top-rated database client that is considered one of the best PostgreSQL editors on the market. 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

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published

PostgreSQL Materialized Views: A Beginner's Guide

author Ochuko Onojakpor tags POSTGRESQL VIEWS 7 MINS 2023-12-04

Postgres List Users: Two Different Approaches 

author Antonello Zanini tags LIST USERS POSTGRESQL 3 MINS 2023-11-16

Using the EXPLAIN plan to analyze Query execution in PostgreSQL

author Bonnie tags EXPLAIN POSTGRESQL 6 MINS 2023-09-21

PostgreSQL's STRING_AGG() Function - A Comprehensive Guide

author TheTable tags POSTGRESQL 3 MINS 2023-09-14

DESCRIBE TABLE PostgreSQL Alternatives

author Antonello Zanini tags POSTGRESQL 5 MINS 2023-09-12

PostgreSQL vs MySQL: The Definitive Comparison

author Antonello Zanini tags MySQL POSTGRESQL 8 MINS 2023-09-08

PostgreSQL JSONPATH: Dealing with the SQL/JSON Path Language

author Antonello Zanini tags JSON POSTGRESQL 7 MINS 2023-09-05

PostgreSQL ISNULL: The Missing Function

author Antonello Zanini tags NULL POSTGRESQL 5 MINS 2023-09-05

How To Kill All Connections to a Database in PostgreSQL

author Antonello Zanini tags CONNECTION POSTGRESQL 4 MINS 2023-08-29

PostgreSQL CTE: What It Is and How to Use It

author Antonello Zanini tags POSTGRESQL 6 MINS 2023-07-17

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗