Introduction to PostgreSQL jsonb
PostgreSQL jsonb is the extended version data type of json data type in PostgreSQL, the main difference of json and jsonb data type is json will store the data into the plain text format while jsonb data type will store the data in binary format. Jsonb requires more space to restore the data into the table also jsonb data type requires more time to build the input presentation. GIN index is used on jsonb data type columns.
Syntax
Below is the syntax of jsonb data type in PostgreSQL.
1. Create a table using jsonb data type
Create table name_of_table (name_of_column1 data_type, name_of_column2 data_type,name_of_column1 jsonb, …, name_of_columnN data_type);
2. Create an index on jsonb data type column
Create index name_of_index on name_of_table using GIN (name_of_column);
Below is the parameter description syntax of jsonb data type in PostgreSQL.
- Create table – This is defined as to create a table by using the jsonb data type column. We have created a table and defined the data type of jsonb to its column.
- Name of the table – This is defined as the name of the table on which table column we have defined the data type as jsonb. Using jsonb data type we are restoring the jsonb data into the table.
- Name of the column – This is defined as a table column from which we have defined the data type as jsonb. We can define any data type to the column at the time of table creation.
- Data type – This is defined as the data type which was we have used on the column at the time of table creation. We are using any data type to the column at the time of table creation in PostgreSQL.
- Create index – This is defined as to create the index in PostgreSQL on jsonb data type column. We have to create the GIN index on the jsonb data type column for faster-accessing data.
- Name of the index – It is defined as the name of the index which was we have to create on the jsonb data type column in PostgreSQL. The name of the index is a very useful and important parameter while creating an index in PostgreSQL.
- Using GIN – This is defined as creating an index on the jsonb data type column by using the GIN index type.
How PostgreSQL jsonb works?
1. In PostgreSQL, the json data type is stored actual copy of the data which was we have inserted into the table whereas jsonb data type stores the binary form of data in binary code.
2. Jsonb data type in PostgreSQL introduced from the version of 9.4. Before the version of 9.4 jsonb data type is not available.
3. To insert data into the jsonb data type column we need to ensure that the data will contain the proper JSON format.
4. We can insert single as well as multiple values at the same time into the jsonb data type column in PostgreSQL.
5. To select the jsonb data type column data in PostgreSQL we have used a select statement same as we have used other data types of column.
6. Also, we have used two operators to query the jsonb type of data in PostgreSQL. Below is the operator available in PostgreSQL to query the jsonb data in PostgreSQL.
- ->
- ->>
7. The first operator is used to return the key to the json object field in PostgreSQL. This operator is very important while querying data.
8. The second operator is used to return the text from the json object field.
9. We can also use the jsonb data type column in the where clause for retrieving data from the column.
10. We have also used the aggregate function such as min, max, average, and sum with jsonb data type.
Examples
Below is the example of jsonb data type in PostgreSQL.
Example #1 – Create a table by using jsonb data type
The below example shows that create a table by using jsonb data type. We have created a table name as jsonb_test.
create table jsonb_test (id int, json_data jsonb);
\d+ jsonb_test;
Example #2 – Create an index on jsonb data type column
The below example shows that create an index on the jsonb data type column. We have created an index on the json_data column.
create index json_idx on jsonb_test using GIN (json_data);
\d+ jsonb_test;
Example #3 – Insert data into jsonb data type column
The below example shows that insert data into the jsonb data type column. We have inserted data into the jsonb_test table.
INSERT INTO jsonb_test (id, json_data) VALUES (1, '{ "customer_name": "ABC", "cust_items": {"cust_product": "Book","qty": 10}}');
select * from jsonb_test;
Example #4 – Insert multiple data into jsonb data type column in a single query
The below example shows that insert multiple data into the jsonb data type column in a single query. We have inserted data into the jsonb_test table.
INSERT INTO jsonb_test (id, json_data) VALUES (1, '{ "customer_name": "ABC", "cust_items": {"cust_product": "Book","qty": 10}}'),
(2, '{ "customer_name": "PQR", "cust_items": {"cust_product": "Notebook","qty": 200}}'),
(3, '{ "customer_name": "XYZ", "cust_items": {"cust_product": "Pen","qty": 150}}'),
(4, '{ "customer_name": "CBD", "cust_items": {"cust_product": "Pencil","qty": 100}}');
select * from jsonb_test;
Example #5 – Select the data from jsonb data type column by using the native operator
The below example shows that select the data from the jsonb data type column by using the native operator.
select id, json_data -> 'customer_name' as Name from jsonb_test;
select id, json_data ->> 'customer_name' as Name from jsonb_test;
Recommended Articles
This is a guide to PostgreSQL jsonb. Here we discuss the Working of jsonb data type in PostgreSQL along with the example. You may also have a look at the following articles to learn more –
