MongoDB or PostgreSQL: The most efficient option for your JSON Data

A dear friend of mine had recently assigned me a task to manage his library. I need to keep a track of each book along with the user details. For each book, there is information such as name, publishing details, the number of books, size, tag, and a unique identification number. Publishing details would consist of the publisher name, edition, and the date of publishing, while size includes the height, weight, and thickness of the book.

Similarly, each user information would include details such as name, address, permissions and user ID. The address and permissions field is multi-value fields with addresses consisting of the street, area, city, zip code, and permissions including groups and premium user types.

Now, being a perfect flag bearer of modern technology, I prefer to store my data using relational databases structure. But my data fields contain multiple values and the traditional database would store a single value for each column in a table. So, I need more than 6 tables and a relational database to store and connect each table.

Quite tedious, isn’t it? Yes, until another friend of mine came as a savior and introduced me to this concept of storing information as documents. He named it something like JSON, and the databases used to store this JSON data as MongoDB, PostgreSQL, etc.

Now honestly, like you, even for me, these terms were just technological jargons, until I did some thorough research and came up with this article to share my knowledge of JSON, MongoDB and PostgreSQL.

What are JSON, MongoDB and PostgreSQL Technologies ?

To begin with, JavaScript Object Notation or JSON is a text-based human-readable data format, derived from the JavaScript scripting language. It is basically used in browser-related applications such as transmitting data over networks, interchanging data between browser and server, providing public data, and other similar applications. With JSON, you can easily dump data as it is into a database.

You can add nested fields within a single data record and add different fields to different data records. Similar to XML, JSON is language independent and provides real-time data reading and decoding. However, features like faster writing time and easier parsing ability provide JSON a competitive edge over XML.

Figure below shows how JSON data is uploaded in BIRD BI tool: 

Figure 1: JSON data uploading in BIRD

MongoDB is a purpose-oriented document-based database built-in 2009 as a breakthrough from the traditional relational database management system. It is an open-source, schema-free database consisting of collections and documents. A document is a set of key-value pairs, similar to a record in a table. A collection is a group of such documents.

However, unlike RDBMS, you can have different fields for different documents in a single collection and different data types within common fields of a collection’s documents. In short, MongoDB provides schema-free environment to create unstructured documents. A query in MongoDB to select records from a table named ‘Book’ would look as follows: db.customer.find ()

Figure below shows MongoDB connection being added in BIRD BI tool:

Figure 2: MongoDB connection in BIRD

PostgreSQL is an open-source object-oriented relational database system developed by Michael Stonebreaker, a computer science professor, as a successor to Ingress. It works on core components like tables, triggers, constraints, roles, stored procedures, and views.

Each record is stored as a row containing set of columns as values. Each record in a table is identified using a primary key and different tables are related using foreign keys. A query in PostgreSQL to select records from a table named ‘Book’ would be as follows: Select * From Book;

Figure below shows PostgreSQL connection added in BIRD BI tool:

Figure 3: PostgreSQL connection in BIRD BI tool

Note that queries in PostgreSQL is similar to SQL queries. 

MongoDB or PostgreSQL for JSON?

There was a time when you would use the only fork to eat pasta and a spoon to eat macaroni until the fork upgraded to spork and you could use it to eat pasta or spoon. In the data world, macaroni is the JSON document data and pasta is tabular relational data.

Till recent years, the spoon aka MongoDB was the native database to store document data while PostgreSQL aka the fork was meant to store relational tabular data. This was until we got the spork! PostgreSQL 9.2 was released, and it was finally possible to use PostgreSQL as a NoSQL database, with native JSON support.

How does PostgreSQL stores JSON? 

With PostgreSQL 9.3, JSON support capability was improved with additional constructor and extractor methods. Its successor version 9.4 started representing JSON data using JSONB, a binary format of JSON. Compared to JSON, JSONB stores data in converted format with the latter being easy and fast to process.

Even though input is slower with conversion overhead, JSONB outsmarts JSON in terms of indexing, containment, and parsing need. JSON data is passed into the database as values and can be accessed using simple SQL queries.

Given is a simple query in PostgreSQL to create a table ‘Books’ and insert data into the table.

Text Box
Figure 4: Query to store JSON data in PostgreSQL

How does MongoDB stores JSON? 

Since 2009, MongoDB has been the native database to store JSON document-based data. Like PostgreSQL, MongoDB also utilizes a binary format to represent JSON data, although uses a different terminology called BSON. BSON is an extended version of the JSON model with additional data types and improved encoding and decoding methods.

With BSON implementation, objects and arrays can be embedded within other objects and arrays. Users are provided a combined advantage of flexible JSON documents along with a high speed lightweight binary format.

JSON data is created and inserted into MongoDB using a highly elastic data model which allows to combine and store multi variate data types without compromising on indexing, validation, and data access rules. Given below is a simple query to insert and create new documents:

Text Box
Figure 5: Query to store JSON data in MongoDB

5 Key Differences between MongoDB and PostgreSQL 

Now that you have learned how both MongoDB and PostgreSQL store JSON data, let me shed some light on few key differences between them through below given infographic:

Figure 6: PostgreSQL vs. MongoDB

MongoDB or PostgreSQL? What do Statistics say? 

According to popular belief, MongoDB is the most popular sought-after database skill for developers in recent years. As per a survey by Statista, about 19.4 percent of respondents prefer to learn MongoDB over other databases. The reason is obvious; non-relational databases are a trend these days with their improved simple design, scaling provision, ease of control over data, and most importantly, the ability to handle unstructured data.

Figure given below shows the worldwide rating of database systems in terms of popularity and demand for learning that skill, as of 2020.

Figure 7: Worldwide rating of database systems…….By Statista

Now, the intriguing fact is the higher rank of PostgreSQL compared to MongoDB among most popular database systems in 2020.

So, what fuels this rating?

Reason is despite emergence of non- relational databases; relational databases are still popular owing to their ease of handling structured data and saving storage space.

Conclusion 

Choosing MongoDB or PostgreSQL for JSON data depends on many factors like applications, dynamic queries, database management software, etc. While MongoDB is a perfect choice for content management, real-time analytics, and IoT applications, PostgreSQL is popular for financial transactional data with its ACID (atomicity, consistency, isolation, and durability) compliance feature.

In reality, there is a dearth of valid reasons to pick up only one database because one type of database might outshine the other in any aspect. And of course, it will be a massive pain in the neck to transit from one track to another. So, better, take your own time, define your database needs, and then come up with an option.

Explore BIRD‘s out of the box data connectors for your JSON data…..