JIYIK CN >

JSONB is a decomposed binary format for storing JSON data. If you follow Google Trends or do some research, you'll see that JSON is becoming increasingly popular in the development world.

Nowadays, NoSQL can store JSON data by default. It is becoming more and more popular because it is robust and we can nest objects inside another object.

Postgres allows users to create JSON and JSONB data types in SQL-type databases.

Creating a table in PostgreSQL using JSONB

Suppose we want to create a table for products. There can be various types of products.

For example, some of them may be in liters, some in grams, or some in pieces. Also, some types of data for a particular product may be different from others.

In this case, it is better to use JSON or JSONB to store this unfamiliar data. Given below is the example of creating a table with JSONB type data:

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

Inserting JSONB data type in PostgreSQL

Earlier, we created a table containing a JSONbcolumn with data of type . Now, let's populate the table.

As we all know, JSONB stores data in key-value format, the same as dictionary in Python. Let's fill 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"}
    }
}'
);

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

In the key data, we have name, priceand ingredient. In 成分, we have the ingredients and their quantities and calorie values.

So, it is a nested object. The motivation here is that we don't need complex joins to get such nested output.

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)

In the command, we have used jsonb_pretty()the function. It formats the JSON object with necessary indentation.

Otherwise the entire JSON appears on one line, making it difficult to understand the relationships between objects.

Pros and Cons of JSONB in ​​PostgreSQL

Although there is a JSON type in Postgres, they have also introduced the JSONB format. As we can see, declarations and other queries are very similar to JSON.

Here are some benefits of using JSONB over JSON:

1. More efficient than JSON.
2. It allows for faster processing since it is a decomposed binary file.
3. Supports indexing, JSON does not.

Besides, it also has some disadvantages. Here are some disadvantages:

1. Slower input.
2. It takes up more disk space than JSON because it leaves a larger footprint.
3. In some cases, aggregate functions can slow down the process.