MongoDB Guide¶

-- List all collections (equivalent to "tables" in SQL)
\collections

-- Examine a collection's structure
\dc users

-- List database statistics
\mstats

-- Simple query to see data
SELECT * FROM users LIMIT 5;

-- SQL syntax that everyone knows
SELECT * FROM users;

-- Translates to MongoDB find operation
-- db.users.find({})

-- Equality and comparison
SELECT * FROM users WHERE age = 25;
-- → {"age": 25}

SELECT * FROM products WHERE price > 100;
-- → {"price": {"$gt": 100}}

SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- → {"created_at": {"$gte": "2024-01-01"}}

-- SQL LIKE patterns translate to MongoDB regex
SELECT * FROM users WHERE name LIKE 'John%';
-- → {"name": {"$regex": "John.*", "$options": "i"}}

SELECT * FROM products WHERE description LIKE '%wireless%';
-- → {"description": {"$regex": ".*wireless.*", "$options": "i"}}

SELECT * FROM codes WHERE code LIKE 'A_B%';
-- → {"code": {"$regex": "A.B.*", "$options": "i"}}

-- Multiple value matching
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');
-- → {"status": {"$in": ["pending", "processing", "shipped"]}}

SELECT * FROM users WHERE age IN (18, 21, 25, 30);
-- → {"age": {"$in": [18, 21, 25, 30]}}

-- Multiple conditions with OR logic
SELECT * FROM users WHERE age > 65 OR status = 'premium';
-- → {"$or": [{"age": {"$gt": 65}}, {"status": "premium"}]}

SELECT * FROM products WHERE price < 10 OR category = 'clearance';
-- → {"$or": [{"price": {"$lt": 10}}, {"category": "clearance"}]}

-- Range queries for numeric and date values
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
-- → {"price": {"$gte": 100, "$lte": 500}}

SELECT * FROM events WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31';
-- → {"event_date": {"$gte": "2024-01-01", "$lte": "2024-12-31"}}

-- Check for missing or null fields
SELECT * FROM users WHERE email IS NULL;
-- → {"$or": [{"email": null}, {"email": {"$exists": false}}]}

SELECT * FROM profiles WHERE bio IS NOT NULL;
-- → {"bio": {"$exists": true, "$ne": null}}

-- Combining multiple conditions
SELECT * FROM shipwrecks
WHERE feature_type LIKE '%Visible%'
  AND depth BETWEEN 0 AND 50
  AND watlev IN ('always dry', 'covers and uncovers')
  OR coordinates IS NOT NULL;

-- Pattern matching with range filtering
SELECT * FROM products
WHERE name LIKE 'iPhone%'
  AND price BETWEEN 500 AND 1500
  AND status != 'discontinued';

-- Multi-condition search
SELECT * FROM users
WHERE (age > 18 OR verified = true)
  AND email IS NOT NULL
  AND role IN ('admin', 'moderator', 'premium');

-- Create a new database
CREATE DATABASE analytics;
-- MongoDB creates databases implicitly when first collection is created

-- Drop an existing database
DROP DATABASE old_database;
-- Permanently removes the database and all its collections

-- Switch to a different database
\c production

-- Create a new collection
CREATE COLLECTION user_profiles;
-- Creates an empty collection in the current database

-- Drop an existing collection
DROP COLLECTION temp_data;
-- Permanently removes the collection and all its documents

-- List all collections
\collections

-- Connect to MongoDB
dbcrust mongodb://localhost:27017/myapp

-- Create a new database
CREATE DATABASE ecommerce;

-- Switch to the new database
\c ecommerce

-- Create collections for our application
CREATE COLLECTION users;
CREATE COLLECTION products;
CREATE COLLECTION orders;
CREATE COLLECTION reviews;

-- Verify collections were created
\collections

-- Clean up test collections later
DROP COLLECTION temp_testing;

-- List all collections with details
\collections

-- Describe collection structure and sample data
\dc users

-- Get detailed database statistics
\mstats

-- List all indexes in the database
\dmi

-- Create an index on a field
\cmi users email
\cmi products name

-- Create a text index for search
\cmi articles content text
\cmi products description text

-- Drop an index
\ddmi users email_1

-- Search for documents containing specific terms
\search articles "mongodb tutorial"

-- Search across multiple fields
\search products "wireless bluetooth"

-- Search with complex terms
\search users "john developer senior"

-- MongoDB find with specific filter
\find users {"active": true, "age": {"$gte": 18}}

-- MongoDB aggregation pipeline
\aggregate orders [
  {"$match": {"status": "completed"}},
  {"$group": {"_id": "$user_id", "total": {"$sum": "$amount"}}},
  {"$sort": {"total": -1}},
  {"$limit": 10}
]

-- Complex find with projection and limit
\find products {"category": "electronics"} {"name": 1, "price": 1} 20

SELECT * FROM users LIMIT 3;

-- Query handles documents with missing fields gracefully
SELECT name, email, phone FROM users;
-- Shows empty string for missing fields

-- Query by ObjectId (automatically detected and converted)
SELECT * FROM users WHERE _id = '507f1f77bcf86cd799439011';
-- → {"_id": ObjectId("507f1f77bcf86cd799439011")}

-- ObjectIds display as hex strings for readability

-- Query array fields
SELECT * FROM users WHERE tags IN ('developer', 'senior');
-- Works with MongoDB array matching

-- Query nested document fields (use dot notation in native MongoDB commands)
\find users {"profile.location": "San Francisco"}

-- Create indexes for better query performance
\cmi users email
\cmi orders user_id
\cmi products {"name": 1, "category": 1}  -- Compound index

-- Create text indexes for search
\cmi articles content text

-- Always use LIMIT for large collections
SELECT * FROM large_collection LIMIT 100;

-- Default limits are applied automatically for safety

-- Error: Database doesn't exist
-- Solution: Create the database first
CREATE DATABASE myapp;
\c myapp

-- If queries return no results, check:
-- 1. Collection exists
\collections

-- 2. Collection has data
SELECT COUNT(*) FROM users;

-- 3. Query conditions are correct
SELECT * FROM users LIMIT 5;

-- For slow queries:
-- 1. Check if indexes exist
\dmi

-- 2. Create appropriate indexes
\cmi users email
\cmi orders user_id

-- 3. Use LIMIT for large result sets
SELECT * FROM large_collection LIMIT 100;