In the last Mongo Matters column, we looked at MongoDB’s new SQL interface for querying data in MongoDB Atlas. Almost all of the “NoSQL” databases today support some form of SQL interface in order to leverage the multitude of SQL-based BI tools and the wealth of SQL expertise in the data analytics market. This “SQL for NoSQL” usually bemuses SQL aficionados and is often seen as some sort of repudiation of the document database concept.
However, the reality is that document databases and SQL databases are actually both converging. Just as JSON-oriented document databases like MongoDB add support for SQL, SQL databases add increasingly strong support for JSON operations.
Let’s compare the JSON support from the two leading open source databases—MongoDB and PostgreSQL.
MongoDB is, of course, a JSON-oriented database from the ground up. Not only does it support rich operations on JSON documents, but the MongoDB Query Language itself is expressed in JSON operations. So MongoDB implements both a JSON store and a JSON API.
PostgreSQL is the leading open-source relational database with roots going back to the 1980s. As a SQL Relational database, the API for the PostgreSQL database is SQL, while data are represented as tables. However, like most SQL databases, PostgreSQL supports a JSON column type—JSONB—which allows JSON objects to be stored directly inside the rows of a table.
Adding JSON documents to a PostgreSQL table is only slightly more complicated than adding documents to a MongoDB collection. It is necessary for PostgreSQL to use a CREATE TABLE statement to create the destination for the documents, while in MongoDB, a collection will be created automatically if it doesn’t already exist. However, this difference is pretty trivial.
When it comes to querying data in JSON documents, MongoDB provides two methods: find for simple queries and aggregate for more complex cases. When searching for matching documents, MongoDB provides a wide variety of operators that can be used to search for matching values in top-level attributes as well as in nested objects and arrays. The PostgreSQL operators provide a similar capability when searching for values at the top level of the document, but the syntax is a bit awkward when searching within nested objects. It may be necessary to nest multiple SELECT statements which unwind or extract the nested objects or arrays before we can find the exact elements that we are looking for.
On the other hand, once you have the data you need, in PostgreSQL, you can use SQL syntax to perform joins and grouping operations that in MongoDB require the aggregation framework. There are plenty of MongoDB users who love the aggregation framework but many more who find the SQL syntax more intuitive and productive.
Indexing within JSONB objects can be tricky as well. MongoDB allows you to create indexes on specific attributes within the document at any level of nesting. In PostgreSQL, you can create a “GIN” (Generalized Inverted Index) index that indexes all of the attributes in a JSONB object, or we can use an “Expression” index that creates an index on a specific JSONB element. Creating the correct index is a lot trickier than in MongoDB, and the PostgreSQL indexes may be less efficient.
When modifying JSON documents, MongoDB provides a lot of operators that allow you to update specific elements within the document. In PostgreSQL, you can set or append values to existing elements within the JSONB, but for anything non-trivial, you may need to SELECT the entire JSONB, manipulate it in your application code, and then UPDATE the JSONB column with the new object. This can require multiple operations where in MongoDB, a single UPDATE statement would suffice.
In short, the JSON support in PostgreSQL is fairly rudimentary when compared to that of MongoDB. However, it’s worth acknowledging that PostgreSQL has a lot of advanced features that are not present in MongoDB either. The SQL dialect is literally decades ahead of the SQL supported by MongoDB, the transactional support is significantly more mature, and support for statement-level parallelism and advanced indexing options are attractive.
Just as MongoDB’s SQL support is bound to improve, so is the JSON support in PostgreSQL. The overlap between the two databases is increasing, and it will be interesting to see if a future version of PostgreSQL could offer a “good enough” experience for MongoDB developers. But for now, MongoDB definitely has the stronger JSON support.