However, this difference is pretty trivial. 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, 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.Īdding JSON documents to a PostgreSQL table is only slightly more complicated than adding documents to a MongoDB collection.

As a SQL Relational database, the API for the PostgreSQL database is SQL, while data are represented as tables. PostgreSQL is the leading open-source relational database with roots going back to the 1980s. So MongoDB implements both a JSON store and a JSON API. Not only does it support rich operations on JSON documents, but the MongoDB Query Language itself is expressed in JSON operations. MongoDB is, of course, a JSON-oriented database from the ground up. Let’s compare the JSON support from the two leading open source databases-MongoDB and PostgreSQL. Just as JSON-oriented document databases like MongoDB add support for SQL, SQL databases add increasingly strong support for JSON operations. However, the reality is that document databases and SQL databases are actually both converging.

This “SQL for NoSQL” usually bemuses SQL aficionados and is often seen as some sort of repudiation of the document database concept. 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. In the last Mongo Matters column, we looked at MongoDB’s new SQL interface for querying data in MongoDB Atlas.

Mongodb json query