PostgreSQL JSONB Datatype and Functions

As in the 9.2 Version, PostgreSQL added a quite basic JSON datatype. Underneath the cover, the JSON type of data is text, with a check that perhaps the layout is correct JSON input similar to XML. Eventually, the team found that the volume of JSON processing and specialized searching needed in PostgreSQL would’ve been difficult or rational to enforce over a textual type of data. Hence, they created a binary representation of the JSON datatype with a complete range of operators and methods. Here comes the JSONB data type. The JSONB datatype is indeed a versatile binary storing layout with complete processing, indexing, and search capability. As a result, it pre-processes JSON information to an inner layout, which has only one value per key and ignores additional whitespace or depression, or you may say indentation. Within this guide, you’ll learn how to interact with the JSONB data form in PostgreSQL, as well as some handy operators and methods for dealing with JSONB data.

The data type you’ll most likely require and choose to use is JSONB, not JSON early version, which is only used for backward compatibility. So, open the PostgreSQL command shell and provide the server’s names, database, port, and username.

Example 01:

Here’s a short illustration of the variations between the two data types. We have to create a table ‘New’ with one of its columns must be ‘JSON’ datatype as follows:

>> CREATE TABLE New(ID serial PRIMARY KEY, Val JSON);

Insert some values into the column ‘Val’.

>> INSERT INTO New(Val) VALUES (‘[1, 2, 3, 4]’), (‘[10, 11, 12, 13]’), (‘{“key”: “value”}’);

Use of ‘@>’ Operator

Once we try to look for entries with an integer in the ‘extra’ column list, we always get an error message as appended below.

>> SELECT * FROM New WHERE Val @> ‘11’;

Yes. JSON is just text and isn’t very efficient, and it doesn’t contain operator configuration. Let the content is changed to jsonb.

>> ALTER TABLE New ALTER COLUMN Val TYPE JSONB;

Now execute the very same query in the shell, and the result shows one row having the number ‘11’ in its array as given below.

>> SELECT * FROM New WHERE Val @> ‘11’;

Example 02:

Let us make a table ‘Bag’ to be used in our illustrations until we start talking about the methods and operators usable for the PostgreSQL JSONB data type. One of its columns, e.g. ‘Brand’ must be ‘JSONB’ datatype as follows:

>> CREATE TABLE Bag(ID serial PRIMARY KEY, Brand JSONB NOT NULL);

We’ll be using the following SQL INSERT declaration to add information into the PostgreSQL table ‘Bag’:

>> INSERT INTO Bag(Brand) VALUES ('{"name": "Gucci", "color": ["red", "black"], "price": 10000, "sold": true,]}'), ('{"name": "Allure", "color": ["red", "Grey"], "price": 85000, "sold": false,]}'), ('{"name": "Kidza", "color": ["black", "white"], "price": 75000, "sold": true,]}');

You can see that the data has been added in the form of a dictionary, e.g., keys and values.

The elements of this table ‘Bag’ can be seen using a SELECT sentence, as seen below:

>> SELECT * FROM Bag;

Use of ‘->’ Operator

Let’s look for the values in the ‘Brand’ column via the key ‘name’ using the ‘->’ operator in our query. It will fetch all the records of the key ‘name’ from the column ‘Brand’. The output will be shown in the new column ‘brand’. The final output will appear as appended below. As you can see, we have three values: ‘Gucci, Allure, kidza’ for key ‘name’.

>> SELECT Brand -> ‘name’ AS brand FROM Bag;

Use of ‘->’ Operator Using WHERE Clause

Let’s fetch all those rows from the table ‘Bag’ where the ‘Brand’ column has got the value ‘true’ for its key ‘sold’. The query for this selection is as follows:

>> SELECT * FROM Bag WHERE Brand -> ‘sold’ = ‘true’;

There you see, the query fetched two rows only from the table ‘Bag’ because it has only two rows having value ‘true’ for the key ‘sold’.

PostgreSQL JSONB Functions:

With JSONB information, there seem to be a variety of in-built methods which can be used. Let’s have a look at them one by one.

JSONB Each Function:

The JSONB Each function takes the data and converts it to the key_value pair. Consider the following query of the jsonb_each method where we have provided values. The highest-level JSON data is expanded into a series of key-value combinations in the outcome. We have got two key-value pairs, as shown below.

>> SELECT jsonb_each('{"name": "Allure", "sold": "true"}’::jsonb );

JSONB Object Keys Function:

Now, we will take a look at the Jsonb_object_keys function. This function takes the data, and itself separates and identifies the key values in it. Try the below SELECT query, in which we have been using the jsonb_object_keys method and provided some values. This method will only return the JSON highest-level document’s keys for the particular data, as shown below.

>> SELECT jsonb_object_keys('{"name": "kidza", "sold": "true"}’::jsonb );

JSONB Extract Path Function:

The JSONB Extract Path function takes the path to show the value in outcome. Try the below query in the command shell, where we have provided ‘brand’ as the path to JSONB jsonb_extract_path method. From the output presented in the image below, you can see that the ‘Gucci’ is a returned value to the path ‘name’.

>> SELECT jsonb_extract_path('{"name": "Gucci", "sold": true}'::jsonb, 'name');

JSONB Pretty Function:

If you’d like to display your JSON files with an easy-to-read layout, then the JSONB Pretty function is the best option. Try the below query, and you will get a simple output.

>> SELECT jsonb_pretty('{"name": "Allure", "sold": false}'::jsonb);

Conclusion:

When you store JSONB information in the PostgreSQL databases, you get the best possible outcome: the simplicity and reliability of a NoSQL database combined with a relational database’s advantages. Utilizing numerous operators and methods, we demonstrated how to use the PostgreSQL JSONB. You’ll be capable of working with JSONB data using our illustrations as a reference.