JSONB in PostgreSQL

1. Create a Table with JSONB in PostgreSQL
2. Insert the JSONB Data Type in PostgreSQL
3. Pros and Cons of JSONB in PostgreSQL

JSONB is a kind of JSON data stored in a decomposed binary format. If you follow the Google trends or do some research, you’ll find that JSON is becoming more and more popular in the development sector.

Nowadays, NoSQL, by default, can store JSON data. As it’s robust to use and we can put nested objects inside another object; hence it is becoming more popular.

Postgres allows users to create JSON and JSONB data types in the SQL type database.

Create a Table with JSONB in PostgreSQL

Let’s say we want to create a table for products. There can be various types of products.

For example, some of them can be measured in liters, some in grams, or some in pieces. Also, there can be some type of data for a specific product that can’t be similar to other products.

In this case, it’s better to use JSON or JSONB to store this unfamiliar data. Creating a table with JSONB type of data is given below:

CREATE TABLE Products(
    id SERIAL PRIMARY KEY,
    seller TEXT NOT NULL,
    data JSONb NOT NULL
);

Insert the JSONB Data Type in PostgreSQL

Previously, we’ve created the table that contains a column with the JSONb type of data. Now, let’s populate the table.

As we know, JSONB stores the data in a key-value format, the same as the dictionary in Python. Let’s populate the above table with some data.

INSERT INTO Products(seller,data)
VALUES
('Jones Heard',
'{ "name": "Milk Shake",
"price": "10$",
"ingredients":
    { "Milk":
        { "amount":"250ml","calorie":"100 Kcl"},
    "Coco Powder":
        { "amount":"50 gram", "calorie":"100 Kcl"}
    }
}'
);

As we successfully inserted the JSONB data into the table, it’s time to view the added data. From the above JSON, we can notice that we have nested objects.

Here in the key data, we have name, price, and ingredient. Inside ingredient, we have ingredients and their amount with the calorie value.

So, it’s a nested object. The motivation here is that we don’t need complex joins to have nested output like this.

SELECT id ,seller, jsonb_pretty(data) as Product_Details
FROM products;

Output:

 id |   seller    |         product_details
----+-------------+----------------------------------
  1 | Jones Heard | {                               +
    |             |     "name": "Milk Shake",       +
    |             |     "price": "10$",             +
    |             |     "ingredients": {            +
    |             |         "Milk": {               +
    |             |             "amount": "250ml",  +
    |             |             "calorie": "100 Kcl"+
    |             |         },                      +
    |             |         "Coco Powder": {        +
    |             |             "amount": "50 gram",+
    |             |             "calorie": "100 Kcl"+
    |             |         }                       +
    |             |     }                           +
    |             | }
(1 row)

Here in the command, we used the jsonb_pretty() function. It formats the JSON object with the necessary indentations.

Otherwise, the whole JSON will appear in one line, and it’s really hard to understand the relations of the objects.

Pros and Cons of JSONB in PostgreSQL

Though there was a JSON type in Postgres, they introduced the JSONB format also. As we saw, the declaration and other queries are quite similar to the JSON.

Here are some benefits while using the JSONB over the JSON:

1. It has better efficiency than JSON.
2. It allows faster processing as it’s decomposed binary.
3. It supports indexing while the JSON doesn’t support it.

Also, it has some drawbacks. Here are the following:

1. Slower input.
2. It takes more disk space than the JSON as it leaves more footprints.
3. In some cases, aggregate functions can slow down processes.

If you want to know more about JSONB, visit this blog. Also, check the official documentation from here.