SQL to MongoDB query mapping.

SQL query pymongo API
SELECT find(projection=)
WHERE find(filter=)
AND $and
OR $or
NOT $neq
IN $in
INNER JOIN find(), find(), find()
LEFT JOIN aggregate($lookup)
UPDATE update_many
DELETE delete_many
INSERT INTO insert_many
CREATE DATABASE implicit
ALTER DATABASE implicit
CREATE TABLE implicit
ALTER TABLE implicit
DROP TABLE drop_collection
CREATE INDEX create_indexes
DROP INDEX drop_index

Performing relational SQL JOINs in MongoDB

Since MongoDB does not have an intrinsic JOIN command the multi-collection JOIN must be done at the application layer. Due to the intrinsic design of MongoDB, if a parallel thread does an update operation on the same set of documents on which JOIN is taking place, there is a possibility of getting different results than expected. This possibility exists in multi-threaded SQL implementations as well.

Application layer multi-collection JOINS without any interleaved database updates are thread safe and can be done in MongoDB. In case you feel SQL joins confusing there is an excellent explanation on how sql joins work which can be referred to before proceeding further.

Here is an example of a typical SQL query generated by Django admin while modifying a user property.

SELECT "auth_permission"."id", "auth_permission"."name", "auth_permission"."content_type_id", "auth_permission"."codename", "django_content_type"."id", "django_content_type"."app_label", "django_content_type"."model" 
FROM "auth_permission" 
INNER JOIN "django_content_type" 
    ON ("auth_permission"."content_type_id" = "django_content_type"."id") 
ORDER BY 
    "django_content_type"."app_label" ASC, 
    "django_content_type"."model" ASC, 
    "auth_permission"."codename" ASC

INNER JOIN translates to:

docs = db['auth_permission'].find(
    {},
    {'content_type_id': True, '_id': False}
).to_list()

joined_docs = db['django_content_type'].find(
    {'id': {'$in': [doc['content_type_id'] for doc in docs]}},
    {'id': True, '_id': False}
).to_list()

# Finally, project the SELECTed fields with content_type_id in joined_docs

So INNER JOIN can basically be done using three application level find operations out of which 2 are for lookups.

Consider a complex multi-join SQL query

SELECT "auth_permission"."id", "auth_permission"."name", "auth_permission"."content_type_id", "auth_permission"."codename" 
FROM "auth_permission" 
INNER JOIN "auth_user_user_permissions" 
    ON ("auth_permission"."id" = "auth_user_user_permissions"."permission_id") 
INNER JOIN "django_content_type" 
    ON ("auth_permission"."content_type_id" = "django_content_type"."id") 
ORDER BY 
    "django_content_type"."app_label" ASC, 
    "django_content_type"."model" ASC, 
    "auth_permission"."codename" ASC

This can be performed using 4 lookups inside python.

docs = db['auth_permission'].find(
    {},
    {'id': True, '_id': False}
).to_list()

joined_docs = db['auth_user_user_permissions'].find(
    {'permission_id': {'$in': [doc['id'] for doc in docs]}},
    {'permission_id': True, '_id': False}
).to_list()

joined_docs2 = db['auth_permission'].find(
    {'id': {'$in': [doc['permission_id'] for doc in joined_docs]}},
    {'content_type_id': True, '_id': False}
).to_list()

joined_docs3 = db['django_content_type'].find(
    {'id': {'$in': [doc['content_type_id'] for doc in joined_docs2]}},
    {'id': True, '_id': False}
).to_list()

# Finally, project the SELECTed fields with id in joined_docs3

LEFT JOIN or LEFT OUTER JOIN can be done using similar lookup operations.