Relational databases such as PostgreSQL are great for storing structured data. This approach has many advantages, but it can lack flexibility. On the other hand, databases such as MongoDB that store JSON-like documents might give you more flexibility than you would like. Fortunately, PostgreSQL offers support for storing and querying loosely-structured JSON data. This article explores the features and benefits of using the JSON and JSONB columns.

Using the json column type

The first column type to look into is JSON. It stores the provided data as is in a text format.

When we insert data, PostgreSQL ensures that it is a valid JSON. If not, it throws an error.

Above, we’ve added our first record to the products column. Since it is a book, it has the publication year and a list of authors. Thanks to using a JSON column, we don’t need to add the publication_year and authors columns to our table. Since the properties column is flexible, we can use it to store any product.

When working with PostgreSQL, we can make parts of our data flexible while putting constraints on other columns. For example, we could add the category_id column to group our products.

If we would only have two types of products, it would make sense to create separate books and cars tables. However, it would get complicated if we had tens of different product types.

Defining a json column with Prisma

To add the json column with Prisma, we need the @db.Json annotation.

productSchema.prisma

Creating the above schema and generating a migration yields the following result:

migrations/20230523001250_products/migration.sql

It’s crucial to acknowledge that when inserting values into a json column, Prisma expects us to provide the data matching the InputJsonValue interface. When we look under the hood of Prisma, we can see that it includes any valid JSON value.

Since we want properties to be an object, we need to enforce the InputJsonObject interface built into Prisma.

createProduct.dto.ts

To achieve the above with the class-validator library, we need to create a custom decorator that checks if the provided value is a valid object.

isJsonObject.ts

Thanks to all of the above, we can now insert JSON data through an API created with NestJS.

Handling null values

Our properties column is nullable and does not need to contain any value. Unfortunately, this can cause confusion because the JSON format can store a null value.

To differentiate between null as a lack of value in the database and as a null value stored in the json column, Prisma introduced the Prisma.JsonNull and Prisma.DbNull constants.

Let’s assume that whenever the user sends null as the value for our properties column, they want to remove the data from the column altogether. To achieve that, we need Prisma.DbNull.

Manipulating the JSON data

PostgreSQL is equipped with quite a few different operators that help us query JSON data. One of the most important ones is ->>, which allows us to get a value of a particular property as text.

We can achieve the same thing with Prisma thanks to the path option.

We can access nested properties by providing the path array with multiple strings such as ['user', 'address', 'street']

Prisma offers a wide variety of filters besides the lt filter, which means “less than”.

The jsonb column

The json column type offers fast insertion because the data we store in the database is stored as text. But, unfortunately, PostgreSQL has to parse the value whenever we perform operations on it, and it causes a performance hit.

To deal with the above issue, PostgreSQL introduced the jsonb column type, which stands for “JSON Binary”. Whenever we store data in the jsonb column, PostgreSQL parses our JSON into a binary format. While this can cause the INSERT operations to be slightly slower, it significantly improves the querying performance. It also doesn’t preserve whitespaces and the order of properties.

Besides the performance improvements the jsonb column provides, it also gives us more operators we can use. It also provides us with additional indexing capabilities.

To create a jsonb column with Prisma, we can use the @db.JsonB annotation.

productSchema.prisma

Since jsonb is the default column used for storing JSON in Prisma we can omit using the @db.JsonB annotation.

Creating the above schema and generating the migration gives us the following code:

migrations/20230524030331_products/migration.sql

Using the jsonb column gives us all of the functionalities of the json type and more.

Summary

In this article, we’ve gone through storing JSON in a PostgreSQL database through raw SQL and Prisma. We also compared the json and jsonb column types. While the above approach gives us a lot of flexibility, it forces us to give up some of the benefits of relational databases. Therefore, we should avoid overusing it and think twice before choosing it as our solution.