What is the difference between JSON and JSONB in PostgreSQL?

PostgreSQL is an open-source DBMS that is comprised of both object and relational models. It has different data types to store and process the data. More information on data types can be found here.

JSON is a human-readable format for storing data. Please note that PostgreSQL has two data types for JSON. Let’s discuss it further below.

Comparison between the data types json and jsonb

The commonality between json and jsonb is that we can store the same data type in both. Handling nested objects in json and jsonb is easier than denormalizing the data into columns or multiple tables.

Let’s discuss the differences between these below:

• Data storage: json stores data in the same format we’ve used, like ASCII/UTF-8. It also undergoes parsing. On the other hand, jsonb stores the data in the binary format, and no reparsing is required when using this data type.

• Query performance: json doesn’t support indexing directly, and it takes much time when data processing is required. On the other hand, jsonb is faster than json whenever data processing is required.

• Duplicate keys: Ordering of the keys and whitespaces are preserved in json. However, jsonb removes the whitespaces from the input data. It also orders the data and store’s the last value in the key in case of duplicates.

Let’s summarize the details in the table below:

Example

Let’s create a table, Animals, with columns animal_id, specifications, and specifications_b. The column animal_id stores the ID of each entry. The column specifications stores the data using the json data type. The column specifications_b stores the data using the jsonb data type. Then, insert a few records into it.

We have inserted the data in the Animals table. Please note that -> and --> are native operators in PostgreSQL. The former is used to return a JSON object, and the latter is used to get the output as text. We can use these operators with json and jsonb.

Let’s write a few queries to retrieve the information from the data. First, retrieve the data from the specifications column, which is of json data type, using the following query:

Let’s do the same for the specifications_b column, which is of jsonb data type, using the following query:

Then, we will retrieve the data where the sound of the animal is roar in the specifications_b column. Have a look at the query below:

Let’s say we want to know the species of the animal where the sound of the animal is roar in the specifications column. Look at the query below:

Let’s do the same with the data in the specifications_b column. Look at the query below:

Next, find the animal without claws in the specifications_b column. In this way, we will access the nested data. Look at the query below:

Please note that there is no difference between the output of both data types. Let’s explore the additional capabilities of jsonb in PostgreSQL. The jsonb data type supports indexing, enabling faster and more efficient queries. We’ll create a GIN index in the following query.

Conclusion

JSON is a text-based format suitable for storing and exchanging data, while JSONB is a binary storage format optimized for efficient querying, indexing, and storage space. The choice between JSON and JSONB depends on query performance, storage efficiency, and the need for dynamic querying or frequent data modifications.