The road so far….

March 28, 2014

Using Postgres JSON

Filed under: java — Rahul Sharma @ 3:03 pm

The applications of today are trying to persist data that has no fixed definition to it like user actions, application responses etc. Since the data is varied in nature the developers pick the Nosql paradigm to keep track of it.Often applications are spring based and spring has awesome support for Mongodb so developers start using Mongo db. Thus now there are multiple databases in the same application and there are overheads of working with the them. The admins have to keep track to two sets, the devs have to keep track to different pojos to make references etc. But do we really need to go down this road ?  I am ardent follower of  Postgres, so if we are using Postgres as the application store I will advise against going this road.

Postgres offers an awesome JSON support which can be used to keep track of schema-less data. The JSON data type checks if the data getting inserted in the column is a valid JSON. We can also make queries on this JSON,as part of SQL where clauses and do some standard aggregations. The best part about this is  we can also integrate it with JPA. So lets see it in action !

We have application for assessments and want to keep a track how the candidate is responding to it Basically we are tracking different properties like if the candidate a has left the window, how many times the answer was changed etc. These all are random properties that the UI is string to keep track of and then saving it for analysis.

Now lets define the candidate table as follows :

ID INTEGER NOT NULL DEFAULT nextval('public.candidate_id_seq'),
-- other required properties

JSON types can not be mapped directly via JPA. There is conversion that is required which is available at ‘mapping json column‘. The mapping is pretty straightforward. Post this the Candidate will be mapped in the following manner:

@TypeDefs({ @TypeDef(name = "StringJsonObject", typeClass = StringJsonUserType.class) })
@Table(name = "CANDIDATE")
public class Candidate {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;
@Column(name = "complete_name")
private String name;
private HashMap<CandidateStatus, Integer> statusCounter;

//getters and setters

@Type(type = "StringJsonObject")
public String getStatusCounterAsString() {
return Utils.getJsonRepresenatation(statusCounter);

public Candidate setStatusCounterAsString(String jsonData) {
if (jsonData != null)
statusCounter = Utils.getObjectFromJson(jsonData, HashMap.class);
return this;


Some important points in the above code are:

  • StatusCounter can be stored as there is not type matching it, thus it is marked as Transient.
  • StatusCount is converted into JSON using method getStatusCounterAsString and is mapped via type ‘StringJsonObject‘.
  • The StringJsonObject is defined at the top of the class

That is it all ! now we can create build a few candidates with some counters and save them. If we want to query based on data in the JSON column we need to write custom SQL for it. Lets say we need to know all candidates who have some blurr counts against their name.

public interface CandidateRepositry extends JpaRepository<Candidate, Long> {

@Query(nativeQuery = true, value = "select * from candidate c where  status_counter->>'BLURR' <> ''")
List<Candidate> findAllCandidatesUsingUnfairMeans();


There are various operators that are used with this. The complete list is available in postgres docs.









  1. Hi i try your sample
    But when i check in database, there are any value when i insert new Component !
    -> No error in the postgresql logs…

    Comment by valighn — January 14, 2015 @ 4:01 pm

    • My sample was referring to a StringJsonType class available in a Stackoverflow thread[1].x Did you copy that class as well ? That class is responsible for converting data to JSON.



      Comment by Rahul Sharma — January 15, 2015 @ 10:16 am

  2. Is it possible to provide full source ?

    Comment by valighn — January 14, 2015 @ 4:02 pm

  3. Hi, thank you.
    I deploy my project in production !

    Comment by valighn — January 15, 2015 @ 8:13 pm

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: