PostgreSQL to Manage JSON

One of the many data types that PostgreSQL support is JSON. Since most of the web APIs communication uses JSON payload immensely, this feature is rather important. Rather than using the plaintext data type to store JSON objects, Postgres has a different data type which is optimized for JSON payloads, verifies that data stored in these fields confirms to the RFC specification. Also in a classic Postgres manner, it allows you to fine-tune your JSON fields for maximum performance.

While creating a table, you will have two options for your JSON column. Plain json data type and jsonb data type, both have their own advantages and disadvantages. We shall go through each of them, by creating a simple table with just 2 columns an ID and a JSON value. Following this we will query data from the table and get a feel for how to manage JSON formatted data inside Postgres.

JSON Data Type

1. Creating a Table with JSON Data Type

Let’s create a simple two column table named users:

Here the column id acts as the primary key, and it will increase in an incremental fashion thanks to the pseudotype serial so we won’t have to worry about manually entering values for id as we go along.

The second column is of json type and is forced to be NOT NULL. Let’s enter a few rows of data to this table, consisting of JSON values.

You can use your prefered JSON beautifier/minifier to convert the JSON payloads above into a single line. So you can paste it at a go into your psql prompt.

The SELECT command at the end showed us that the rows were successfully inserted into the users table.

2. Querying JSON Data Type

Postgres allows you to dig into the JSON payload itself and retrieve a particular value out of it, if you reference it using the corresponding value. We can use the -> operator after the json column’s name, followed by the key inside the JSON object. Doing so
 
For example, in the table we created above:

You may have noticed the double quotes in the column containing emails. This is because the -> operator returns a JSON object, as present in the value of key “email”. Of course, you can return just text, but you will have to use the ->> operator instead.

The difference between returning a JSON object and a string becomes clear once we start working with JSON objects nested inside other JSON objects. For example, I chose the “personalDetails” key to intentionally hold another JSON object. We can dig into this object too, if we want:

This can let you go as deep into the JSON object as you would want to. Let’s drop this table and create a new one (with the same name) but with JSONB type.

JSONB Data Type

Except for the fact that during creation of the table we mention jsonb data type instead of json, all else looks the same.

Even the insertion of data and retrieval using the -> operator behaves the same way. What has changed is all under the hood and noticeable in the table’s performance. When converting JSON text into a jsonb, Postgres actually turns the various JSON value types into native Postgres type, so not all valid json objects can be saved as valid jsonb value.

Moreover, jsonb doesn’t preserve the whitespaces, order of json keys as supplied by the INSERT statement. Jsonb actually converts the payload into native postgres binary, hence the term jsonb.

Of course, insertion of jsonb datum has a performance overhead because of all these additional work that postgres needs to do. However, the advantage that you gain is in terms of faster processing of the already stored data, since your application would not have the need to parse a JSON payload everytime it retrieves one from the database.

JSON vs JSONB

The decision between json and jsonb sole depends on your use case. When in doubt use jsonb, since most applications tend to have more frequent read operations that write operations. On the other hand, if you are sure that your application is expected to do more synchronous write operations than read, then you may want to consider json as an alternative.

Conclusion

People working with JSON payloads and designing interfaces for Postgres storage will benefit immensely from this particular section of their official documentation. The developers were kind enough to furnish us with jsonb indexing and other cool features which can be leveraged to improve the performance and simplicity of your application. I implore you to investigate these as well.
 
Hopefully, you found this brief introduction of the matter helpful and inspiring.