Postgres Unstructured Database using JSONB: Mastering the Art of Code

I recently ran into a very specific problem in my use-case. 

Story behind the problem:

We had developed a backend with dynamic APIs and its corresponding queries to be run on the customer's database. Only problem being, we had no idea what the database might look like.

So I created a table that looked like this:

Endpoint	Query	Description
/getAllAccounts	select * from accounts	get all data from accounts table
/getAccountsByRoleType	select * from accounts where role_type=[role]	query the table according to role and get the data

And I created a backend using Springboot and JAVA to get required data for queries that had [parameter] in them and process them accordingly. Hence, for any query, I could return its corresponding queried data in JSON format.

So the endpoint for parameterized queries would look like this:

/getAccountsByRoleType?role=Admin

Good, now thats done, I would need to collect this data and save it in my own Database(future needs, don't ask why).

So, now I have to save the data from their table into my own, the structure of the data being dynamic and unknown. So, I guess we are left with only one option - nosql database.

Whats standing in the way? Well, I am using Postgres for the rest of my applications, hence there is a dependency there.

Now we come to the problem of saving unstructured data in a structured DB. Lets begin.

Scouring the net, I decided on a few ways we could implement this:

Option 1:

We could save the unstructured data as JSON string in a column.

That seems easy enough, although we will have to pull all data and iterate through every row to find specific queries within each JSON.

Quite a lot of overhead for a data dump of about 70K to 80K records. Lets keep this aside for now.

Option 2:

Solution that I found for quick filtering queries? We can use Postgres's JSONB datatype for this use case. JSONB or Binary JSON saves the Object as a binary and indexes all the keys at the time of insertions. So, this indexing adds a minimal overhead while insertions. Tests suggest it is very insignificant at 70K to 80K records to worry about.

This seems like a viable option. Lets look at how to use this.

How to use JSONB with Java Springboot project?

Setting up Postgres table-

First of all, you need Postgres version 9.4 and up. 

Next, you can simply give the data type as JSONB while creating the table as such:

1	CREATE TABLE karnage(id int, data JSONB, created_date Date);

Now that your Postgres has the correct datatype, you need to connect it to Java Springboot's JPA repository.

Keeping in mind that JPA doesn't provide advanced data types in its repository, we will need to use an external data type library. For this, I have come across an excellent library called "hibernate-types" by Vladmihalcea. You can access it via maven or his git page.

1 2 3 4 5

<dependency> <groupId>com.vladmihalcea</groupId> <artifactId>hibernate-types-52</artifactId> <version>${hibernate-types.version}</version> </dependency>







git Link: Hibernate Types git




Now to the next step, setting up your JPA repository in the Springboot Application.

In your Entity class, set up the annotations like so:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

import com.vladmihalcea.hibernate.type.json.JsonBinaryType; @Entity @Table(name="karnage") @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class) public class KarnageEntity{ @Id @GeneratedValue(strategy=GenerationType.AUTO) //Only Needed if ID is     @Column(name="ID")                        //handled by persistence library private long id;     @Type(type = "jsonb")     @Column(name="DATA")     private String data; //Might be a String but we will push JSONObject     @Column(name="created_date")     private Date createdDate; }

This is how you set up an Entity with hibernate to handle JSONB datatype.

How to Filter Data from the data column and use the set up as an unstructured database?

To filter the data from the JSONB data column, we need a normal query albeit, with a little twist.

Lets look at how a JSONB query looks like:-

SELECT * FROM karnage WHERE and data ->> 'key'='value';

Select data ->> 'key' from karnage;

So, we need to search for a value to a key using the "data->>" for the JSONB column. 

Note: 

If we use ->>, we will only return the result as text. To return JSONB object, use ->

So there we have it, the complete guide to using JSONB and handling unstructured data in a structure database with Springboot.

Thanks for reading!