Handling JSON data in PostgreSQL using Springboot JPA application

I am sure you would have worked on a lot of Spring Boot applications which perform CRUD (Create, Read, Update, Delete) operations on structured data having a fixed schema using RDBMS systems like PostgreSQL. But, what if you would like to use JSON data in an RDBMS system?

Recently, I tried to perform CRUD operations on unstructured & schema-less JSON data in PostgreSQL and I would like to share my discovery with all of you. This feature was introduced by PostgreSQL when NoSQL databases were getting a lot of popularity over traditional RDBMS systems. Even if you worked with JSON data in an RDBMS system, updating a json’s specific key is something that many people skip and they directly update the entire json. This article covers all those rare things which are not easy to perform or even find on the web.

Article Outline: In the end, we will have a REST Controller with CRUD APIs to Create, Read, Update and Delete JSON data in PostgreSQL. This includes updating specific key-value pair of JSON, adding a new key-value pair into the JSON, removing a key-value pair from JSON etc using functions like to_json() and json_set().

I swear that you won't find an example like this anywhere on the web unless someone clones my repository itself.

Here is the link to a sample Spring Boot application that I wrote. If you would like, you can follow the below steps to create this project on your own.

Let’s get started!

1. Go to https://start.spring.io/ and generate a Spring Boot project with the below-mentioned dependencies.

2. Spin up a PostgreSQL docker container to connect with our Spring Boot application by following steps 1–4 from here.

3. Update the application.properties as mentioned below. Please note that we wish to write JSON data into the Postgresql database and hence, we are using “stringtype=unspecified” in spring.datasource.url field.

4. Create entity classes as below. Student table has a jsonb type column favs. You can read more about jsonb datatype on the web, but it's the same as json with slight improvements. One of the main advantages of using jsonb over json in our case is that jsonb type data can make use of functions like jsonb_set() available in PostgreSQL which helps us in updating the key-value pairs in json (not entire json). If you wish, you can make use of JSON type column along with the json_set() function in MySQL.

Address has 2 variables: City and State. Both, City and State have been marked as composite Primary Key for the Student table using Embeddable and EmbeddedId

5. Create the JPA Repository class as mentioned below. Try to spend some time and analyse the custom queries that I have written. If required, use this PostgreSQL documentation in order to understand the functions like to_json() and json_set().

6. Create the Service class and interface. Here is a glimpse of it:

7. Lastly, create the Controller class. Here is a glimpse of it:

8. In order to take requests from user and submit the request to Controller, we have to create a Domain class called StudentRequest:

That’s it. We are all set.

Build the Spring Boot application:

mvn clean install

Run the Spring Boot application:

mvn spring-boot:run

Test the Spring Boot application APIs

We have the PostgreSQL database running from step 2. We have started the Spring Boot app as well. Let us try to perform the CRUD operations using Postman.

Download the Postman collection JSON file from my Github. Open Postman and import the Postman collection named “JPAwithJSON.postman_collection.json”.

Try to hit Create a new Student API, Get all Students API, etc

Hope you enjoy this new exploration of using JSON in RDBMS systems with Spring Data JPA.

Like, Share this article and do follow me on Linkedin.