JSONBs in Postgres and more

JSONB – short for binary JSON – is a data type introduced to the world by PostgreSQL in early 2014… yes, that old.

It allows for storing complex and nested data structures in several relational databases. It is similar to the JSON data type, but with added functionality and performance benefits.

One major advantage of using JSONB over JSON is its ability to be indexed and queried directly within the database, making it more suitable for use in applications with high-performance requirements.

Additionally, JSONB supports more advanced features such as data validation and schema enforcement, making it a more robust option for storing and manipulating large amounts of semi-structured data.

In this article, we will dive deeper into the capabilities and use cases of JSONB, specifically in the context of PostgreSQL.

We will also explore JSONB support in other popular databases and how it can be used in application development.

JSONB in PostgreSQL

PostgreSQL is one of the most popular open-source relational databases and has built-in support for JSONB. In PostgreSQL, JSONB columns are created just like any other data type, using the jsonb type in the CREATE TABLE statement.

Once a JSONB column is created, it can be manipulated just like any other data type. Data can be inserted into a JSONB column using the standard INSERT statement and queried using the SELECT statement. However, PostgreSQL also provides a number of additional functions and operators specifically for working with JSONB data.

One of the most powerful features of JSONB in PostgreSQL is the ability to create indexes on JSONB columns, allowing for faster querying and searching of the data. This can be done using the CREATE INDEX statement and specifying the jsonb_path_ops operator class.

PostgreSQL also provides a number of functions and operators specifically for working with JSONB data. These include extracting specific keys or values, comparing JSONB data to other JSONB or non-JSONB data, and even modifying specific parts of the JSONB data.

Advanced features such as data validation and schema enforcement, can also be performed on JSONB data in PostgreSQL. This can be done by creating check constraints on the JSONB column or by using the JSON Schema functionality provided by the jsonb_path_ops operator class.

The JSONB data type in PostgreSQL provides a powerful and flexible way to store and manipulate complex and nested data structures within the database, with added functionality – but other RDBMSs were quick to follow.

JSONB in MySQL

While PostgreSQL first introduced us to JSONB, other popular databases have their own implementations and features for working with JSON or JSON-like data.

MySQL has supported JSON since version 5.7.8. The data is stored in binary format, similar to PostgreSQL's JSONB. This ensures that the JSON data is validated and efficiently accessed as it's parsed and optimized into keys, values, and arrays.

However, if there are multiple fields with the same key, only the last one will be kept. Also, MySQL does not support indexing for JSON columns, which can lead to slow search results due to a full table scan.

One solution is to create computed or virtual columns, as demonstrated by MySQL in their documentation.

Another popular RBDMS has made a name for itself in the realm of JSON datatypes: MongoDB.

JSONB in MongoDB

MongoDB is a NoSQL database that stores data in a JSON-like format known as BSON (Binary JSON), which can be queried and indexed in similar ways to JSONB in PostgreSQL.

There are some differences between MongoDB and Postgres though.

BSON format in MongoDB has a max limit of 64 bits for int/float numbers, while Postgres' JSONB format doesn't have such a restriction. Postgres offers constraint/validation functions for meaningful JSON documents, but MongoDB doesn't.

MongoDB supports automatic sharding for easy horizontal scaling, while Postgres' scaling is mostly vertical but can be horizontal with outside help. MongoDB allows for higher write throughput by deferring disk writing, but with possible data loss.

Postgres has more flexibility, allowing data to be stored in either a JSON column for later modeling or an SQL schema table within the same database.

Forward

When choosing a database for a project that requires the use of JSONB, it is important to consider the specific JSONB functionalities and performance that are needed.

For example, PostgreSQL is a great choice for projects that require advanced querying and indexing of JSON data, while MongoDB may be a better option for projects that require a NoSQL database with built-in support for JSON-like data.

The issue with MYSQL is its inability to index, which in all honesty takes it out of the race for those that intend on making JSON a centerpiece of their projects.

In other words, it is important to consider the specific JSONB functionalities and performance that are needed when choosing a database for a project.

Interested in learning more about Forest Admin and how we can help your business thrive?

If you feel like chatting with us because you're curious about what we do, please reach out.

If you want to know more about various Databases, here are a few articles we’ve written on the subject:

1. MongoDB vs MySQL
2. Most popular PostgreSQL GUIs of 2022
3. Most popular MariaDB GUIs
4. MongoDB cheat sheet
5. PostgreSQL Data types and more