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:
jsonstores data in the same format we’ve used, like ASCII/UTF-8. It also undergoes parsing. On the other hand,jsonbstores the data in the binary format, and no reparsing is required when using this data type. -
Query performance:
jsondoesn’t support indexing directly, and it takes much time when data processing is required. On the other hand,jsonbis faster thanjsonwhenever data processing is required. -
Duplicate keys: Ordering of the keys and whitespaces are preserved in
json. However,jsonbremoves 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:
Comparing JSON and JSONB
JSON | JSONB | |
Storage | Preserves the format | Changes it to binary format |
Parsing time | Less | More |
Reparsing required | No | Yes |
Indexing | Doesn't support directly | Supported |
Data processing | Slow | Fast |
Whitespaces | Preserves whitespaces | Removes whitespaces |
Ordering of keys | Preserved | Ordered by keys |
Duplicates | Preserved | Not allowed (Last assigned value will be stored in the key) |
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.
CREATE TABLE Animals (animal_id SERIAL PRIMARY KEY,specifications JSON,specifications_b JSONB);INSERT INTO Animals (specifications, specifications_b)VALUES('{"name": "Hope", "species": "chicken", "sound": "cluck", "traits": {"claws": "true", "fin": "false", "feathers": "true"}}' :: json, '{"name": "Hope", "species": "chicken", "sound": "cluck", "traits": {"claws": "true", "fur": "false", "feathers": "true"}}' :: jsonb),('{"name": "Daisy", "species": "fish", "sound": "N/A", "traits": {"claws": "false", "fin": "true", "feathers": "false"}}' :: json, '{"name": "Daisy", "species": "Fish", "sound": "N/A", "traits": {"claws": "false", "fin": "true", "feathers": "false"}}' :: jsonb),('{"name": "Max", "species": "duck", "sound": "quack", "traits": {"claws": "true", "fin": "false", "feathers": "true"}}' :: json, '{"name": "Max", "species": "duck", "sound": "quack", "traits": {"claws": "true", "fin": "false", "feathers": "true"}}' :: jsonb),('{"name": "Bella", "species": "cat", "sound": "meow", "traits": {"claws": "true", "fin": "false", "feathers": "false"}}' :: json, '{"name": "Bella", "species": "cat", "sound": "meow", "traits": {"claws": "true", "fin": "false", "feathers": "false"}}' :: jsonb),('{"name": "Rocky", "species": "lion", "sound": "roar", "traits": {"claws": "true", "fin": "false", "feathers": "false"}}' :: json, '{"name": "Rocky", "species": "lion", "sound": "roar", "traits": {"claws": "true", "fin": "false", "feathers": "false"}}' :: jsonb);
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:
SELECT specificationsFROM Animals;
Let’s do the same for the specifications_b column, which is of jsonb data type, using the following query:
SELECT specifications_bFROM Animals;
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:
SELECT specifications_bFROM AnimalsWHERE specifications_b -> 'sound' ? 'roar';
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:
SELECT specifications ->> 'species' AS speciesFROM AnimalsWHERE specifications ->> 'sound' = 'roar';
Let’s do the same with the data in the specifications_b column. Look at the query below:
SELECT specifications_b ->> 'species' AS speciesFROM AnimalsWHERE specifications_b ->> 'sound' = 'roar';
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:
SELECT animal_id, specifications_bFROM AnimalsWHERE specifications_b -> 'traits' ->> 'claws' = 'false';
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.
-- Create an index on a JSONB columnCREATE INDEX idx_specsON AnimalsUSING GIN (specifications_b);-- Query using the indexSELECT specifications_bFROM AnimalsWHERE specifications_b ->> 'species' = 'cat';
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.
Free Resources
