JSON
POSTGRESQL

JSON vs. JSONB in PostgreSQL: A Complete Comparison

intro

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
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

Now, you may be wondering why PostgreSQL has two JSON data types, what the main differences between them are, 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 doubts!

In this PostgreSQL json vs jsonb guide, you will learn:

  • What the json data type is and how it works
  • What the jsonb data type is and why it was introduced
  • What the two data types have in common and where they differ
  • When to use jsonb vs json and vice versa

Let’s dive in!

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.

At the same time, the introduction of the json data type was not a revolution...

The reason is that the PostgreSQL json data type is not much more than a simple text field. Read our article to learn the difference between the Postgres text vs varchar data types.

In particular, 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. However, 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 ALTER TABLE query:

Copy
        
1 ALTER TABLE "users" ADD COLUMN "log" json;

Then, you can insert data in the log column as in the example below:

Copy
        
1 INSERT INTO "users"("log")VALUES('{ "loggedAt": "2022-04-06 12:09:51.654000 +00:00", "attempts":[{"number": "1", "status": "failed"}, {"number": "2", "status": "success"}] }');
Performing the two queries above in DbVisualizer
Performing the two queries above in DbVisualizer

This is pretty much everything you can do with json.

Time to explore the PostgreSQL jsonb data type!

JSONB Data Type

PostgreSQL added the jsonb data type in Postgres 9.4, which is when the JSON support in PostgreSQL became real. Note that the 'b' at the end of the data type name stands for 'better'. That is because jsonb stores JSON data in a special binary representation, whose format is compressed and more efficient than text. For all information, read our guide on jsonb.

In detail, jsonb is based on an optimized format that supports 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 take a look at all the built-in jsonb functions offered by PostgreSQL in the official documentation. Additionally, check out our guide on PostgreSQL JSONPATH.

On top of those operations, jsonb also supports an existence operator. This gives you teh ability to test whether a string appears as an object key or array element. You can use it as follows:

Copy
        
1 SELECT'["postgresql", "mysql", "oracle"]'::jsonb ? 'postgresql';

The above query verifies if the JSON array includes the 'postgresql' string element. Run the query, and you will get a true result:

Note the true result
Note the true result

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 ALTER TABLE statement below:

Copy
        
1 ALTER TABLE "users" ADD COLUMN "config" jsonb;

Then, you can insert data in such a column as below:

Copy
        
1 INSERT INTO "users"("config") 2 VALUES('{"subscriptionLevel": "hero", "points": 450, "achievements": [4, 7, 9], "skins": {"special": true, "hallowing": true, "christmas": false}}');
Executing the two JSONB queries in DbVisualizer
Executing the two JSONB queries in DbVisualizer

Wonderful, now that you know what these two data types for dealing with JSON data are, you are ready to delve into the Postgres jsonvs jsonb comparison!

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 that, 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 scenes. Specifically, the json data type stores an exact copy of the input text. Thus, each function and operation has to reparse the entire field at each execution. Instead, jsonb relies on a more efficient format, which makes it inherently faster than json.

Still, the different behavior of json 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.

Instead, jsonb stores data in a decomposed binary format. This makes INSERT statements a bit slower compared to json due to conversion overhead. However, the jsonb binary format is significantly faster to process as it does not involve reparsing. Also, jsonb supports many more functions and operators than json.

Moreover, jsonb supports indexing, which 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. This is the last step of this PostgreSQL json vs jsonb comparison!

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 especially true if you already know that your log data comes in valid JSON format. For that scenario, text is also a viable option.

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 vs 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, the popular and feature-rich database client, fully supports all PostgreSQL data types. DbVisualizer is a top-rated PostgreSQL client that comes with advanced features like query optimization and ERD-like schema export. Download DbVisualizer for free!

Thanks for reading! We hope that you found this article helpful.

FAQ

What are the main elements to keep in mind in the json vs jsonb Postgres comparison?

Storage Format:

  • json stores data as plain text.
  • jsonb stores data in a binary format, which is more efficient for querying.

Performance:

  • jsonb is faster for searching operations.
  • json is slower for these tasks since it requires re-parsing.

Flexibility:

  • json preserves the exact text formatting.
  • jsonb may reorder keys and remove whitespace.

Indexing:

  • jsonb supports indexing on JSON fields.
  • json does not support direct indexing.

Which is better between jsonb vs json?

jsonb is generally better than json in PostgreSQL for most use cases. It stores data in a binary format, making it faster and more efficient for querying, indexing, and operations. jsonb also supports indexing, which improves performance for search and retrieval tasks. However, json might be preferable if you need to preserve the exact text formatting of the JSON data, as jsonb may reorder keys and remove whitespace.

Which wins the PostgreSQL json vs jsonb performance race?

jsonb wins the performance race as it stores JSON data in a binary format, making it more efficient for querying compared to json, which stores data as plain text. Plus, jsonb supports indexing on JSON fields, leading to faster search and retrieval, while json requires re-parsing the text for operations, resulting in slower performance.

Dbvis download link img
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
title

Postgres DATEDIFF Equivalent Functions: Calculating Date and Time Differences

author Lukas Vileikis tags POSTGRESQL 6 min 2024-11-04
title

CREATE SCHEMA PostgreSQL Statement: What, Why & When to Use

author Lukas Vileikis tags POSTGRESQL 4 min 2024-10-30
title

SQL EXPLAIN: The Definitive Tool to Optimize Queries

author Antonello Zanini tags MySQL POSTGRESQL SQL 12 min 2024-10-29
title

SQL Not Equal Operator: Definitive Guide with Examples

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-10-14
title

PostgreSQL Subquery - A Guide

author Leslie S. Gyamfi tags POSTGRESQL SUBQUERY 3 min 2024-10-02
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

A Guide to the Postgres Not Null Constraint

author Leslie S. Gyamfi tags POSTGRESQL 3 min 2024-09-25
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

PostgreSQL NULLIF: Conditional Logic Made Easier

author Antonello Zanini tags NULLIF POSTGRESQL 6 min 2024-09-11
title

The Postgres UPDATE Statement: A Deep Dive

author Leslie S. Gyamfi tags POSTGRESQL 4 min 2024-09-05

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 ↗