SQL to NO-SQL mappings

Terminology and Concepts

The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.

SQL Terms/Concepts	MongoDB Terms/Concepts
database	database
table	collection
row	document or BSON document
column	field
index	index
table joins	$lookup, embedded documents
primary key Specify any unique column or column combination as primary key.	primary key In MongoDB, the primary key is automatically set to the _id field.
aggregation (e.g. group by)	aggregation pipeline See the SQL to Aggregation Mapping Chart.
SELECT INTO NEW_TABLE	$out See the SQL to Aggregation Mapping Chart.
MERGE INTO TABLE	$merge (Available starting in MongoDB 4.2) See the SQL to Aggregation Mapping Chart.

Create and Alter¶

The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB statements.

SQL Schema Statements	MongoDB Schema Statements
CREATE TABLE people ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(30), age Number, status char(1), PRIMARY KEY (id) )	Implicitly created on first insertOne() or insertMany() operation. The primary key _id is automatically added if _id field is not specified. db.people.insertOne( { user_id: "abc123", age: 55, status: "A" } ) However, you can also explicitly create a collection: db.createCollection("people")
ALTER TABLE people ADD join_date DATETIME	Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level, updateMany() operations can add fields to existing documents using the $set operator. db.people.updateMany( { }, { $set: { join_date: new Date() } } )
ALTER TABLE people DROP COLUMN join_date	Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level, updateMany() operations can remove fields from documents using the $unset operator. db.people.updateMany( { }, { $unset: { "join_date": "" } } )
CREATE INDEX idx_user_id_asc ON people(user_id)	db.people.createIndex( { user_id: 1 } )
CREATE INDEX idx_user_id_asc_age_desc ON people(user_id, age DESC)	db.people.createIndex( { user_id: 1, age: -1 } )
DROP TABLE people	db.people.drop()