Using JSON Objects With PostgreSQL

Although it’s not very common, sometimes it does make sense to store you data as a JSON object. If that is the case then PostgreSQL now has you covered, since version 9.2 PostgresSQL has offered support for storing your data as JSON objects. With version 9.3 PostgresSQL started to also support JSON functions which could parse through that data. Now if you are wondering why you would want to use PostgreSQL and not a no-sql database to store you JSON object, I give you two reasons:

  1. PostgresSQL provides you with the ability to store your JSON object inside a relational database which means you can actually still create relationships to that data.

  2. Perhaps more important to people who insist on using MongoDB and other no-sql databases due to its speed is the fact that PostgreSQL in “The newest round of performance comparisons of PostgreSQL and MongoDB produced a near repeat of the results from the first tests that proved PostgreSQL can outperform MongoDB.“ http://www.enterprisedb.com/postgres-plus-edb-blog/marc-linster/postgres-outperforms-mongodb-and-ushers-new-developer-reality

Let’s take a look at a couple of examples of PostgresSQL JSON functionality that we can use.

Here are the default operators

operators

First let’s just look at a normal insert:

CREATE TABLE user ( id integer, data json );

INSERT INTO user VALUES (1,
  '{ "name":  {first: "Paulo"}, "email": { "pdiniz@pdiniz.com" } }');

Notice that the user table has an id value associated with the json object which is not inside the json object itself. This right here is exactly what makes PostgreSQL’s solution to JSON objects the go to solution. This id means that you can now use it In order to create relationships to other tables inside a database that actually understands what these relationships mean.

Let’s select embedded data:

SELECT id, data->'name'->>'first' as first_name FROM user;

One very short line of code and we got all the data we needed. This is almost as simple as using dot notation in JavaScript. Do you remember what had to be done to do the same thing in MongoDB? If not check out my blog post on that here.

Now let’s look how easily we can filter based on nested objects:

SELECT * FROM user WHERE data->'name'->>'first' = 'Paulo';

Again look at how easy that was, with one added bonus this is pretty much a pure sql select statement, you don’t need to learn another syntax or how to use a no-sql database. You are getting a faster and easier implementation right inside a relational database.

If this hasn’t convinced you to switch to JSON data from a MongoDB to a PostgreSQL implementation yet then consider one last small piece of information - PostgreSQL is actually RELIABLE and PRODUCTION ready. With a PostgreSQL you don’t need to set up a three server cluster because your database can’t be relied on to store data properly, and you don’t need to set up Cron jobs to continuously restart your database because it can’t really be trusted to stay on. If you would like to know more about PostgresSQL advances in document storage head over to their website. And if you would like to know more about the functions and operators which are available to you go to http://www.postgresql.org/docs/9.4/static/functions-json.html.

Paulo Diniz