Prepared for internal tech talk.

Simple select

SELECT * FROM people
db.people.find()

Projection

SELECT user_id, status
FROM people
db.people.find(
    { },
    { user_id: 1, status: 1, _id: 0 }
)

Conditions:

SELECT *
FROM people
WHERE status = "A"
AND age = 50
SELECT *
FROM people
WHERE status = "A"
OR age = 50
SELECT *
FROM people
WHERE user_id like "bc%"
db.people.find(
    { status: "A", age: 50 }
)
db.people.find({ 
    $or: [ 
        { status: "A" } , 
        { age: 50 } 
    ] 
})
db.people.find( { user_id: /^bc/ } )

Order

SELECT *
FROM people
ORDER BY user_id ASC, create_date DESC
db.people.find().sort( { user_id: 1, create_date: -1 } )

Limit

SELECT *
FROM people
LIMIT 100, 10
db.people.find()
    .limit(10)
    .skip(100)

Explain

EXPLAIN SELECT *
FROM people
WHERE status = "A"
db.people.find( { status: "A" } ).explain()

Count

SELECT count(*)
FROM people
db.people.count()

Aggregations

Distinct one field

SELECT DISTINCT(status)
FROM people
db.people.aggregate([ 
    { $group: { _id: "$status" } }
])
SELECT DISTINCT(status)
FROM people
WHERE status = "A"
db.people.aggregate([
    { $match: { author: "dave" } }, 
    { $group: { _id: "$status" } }
])

Examples

SELECT invoice_month, invoice_year, sum(id), sum(gross_total)
FROM invoice
WHERE status in ("PUBLISHED", "PAID")
GROUP BY month(invoice_date) as invoice_month, year(invoice_date) as invoice_year
db.getCollection('invoice').aggregate([
    {
        "$match": {
            "status": {"$in": ["PUBLISHED", "PAID"]},
        }
    }, 
    
    {
        "$addFields": {
            "grossTotal": {"$toDecimal": "$grossTotal"},
            "month": {"$dateToString": {"format": "%Y-%m", "date": "$createdTime"}}
        }
    }, 
    
    {
        "$group": {
            "_id": "$month",
            "quantity": {"$sum": 1},
            "grossTotal": {"$sum": "$grossTotal"},
        }
    }, 
    
    {
        "$sort": {"_id": 1}
    }
])
SELECT invoice_row.code, invoice_row.name, sum(quantity), sum(grossTotal)
FROM invoice_row
    JOIN invoice ON invoice_row.invoice_id = invoice.id
WHERE invoice.status in ("PUBLISHED", "PAID")
GROUP BY invoice_row.code, invoice_row.name
LIMIT 5;
db.getCollection('invoice').aggregate([
    {
        "$match": {
            "status": {"$in": ["PUBLISHED", "PAID"]}
        }
    },
    // Get internal collection as separate objects
    {
        "$unwind": "$items"
    },
    {
        "$replaceRoot": {"newRoot": "$items"}
    },
    {
        "$addFields": {
            "_group_code": {
                "$cond": {
                    "if": {
                        "$ne": ["$code", null]
                    },
                    "then": "$code",
                    "else": "$article"
                }
            },
            "quantity": {"$toDecimal": "$quantity"},
            "grossTotal": {"$toDecimal": "$discounted.gross.total"}
        }
    },

    {
        "$group": {
            "_id": "$_group_code",
            "code": {"$first": "$code"},
            "name": {"$first": "$article"},
            "quantity": {"$sum": "$quantity"},
            "grossTotal": {"$sum": "$grossTotal"}
        }
    },

    // Sort results
    {
        "$sort": {
            "grossTotal": -1
        }
    },

    // Apply limit
    {
        "$limit": 5
    }
])
db.getCollection('invoice').aggregate([
    {
        "$match": {
            "status": {"$in": ["PUBLISHED", "PAID"]},
        }
    }, 
    {
        "$addFields": {
            "grossTotal": {"$toDecimal": "$grossTotal"}
        }
    }, 
    {
        "$group": {
            "_id": "$client",
            "quantity": {"$sum": 1},
            "grossTotal": {"$sum": "$grossTotal"}
        }
    }, 
    {"$sort": {"grossTotal": -1}}, 
    {"$limit": 5}
])

Invoices could be in status PAID, PUBLISHED, DRAFT, CANCELED. DRAFT and CANCELED shouldn’t appear in statistics.

db.getCollection('invoice').aggregate([
    {
        "$match": {
            "status": "PUBLISHED"
        }
    }, 
    {
        "$addFields": {
            "grossTotal": {"$toDecimal": "$grossTotal"}
        }
    }, 
    {
        "$addFields": {
            "lateGrossTotal": {
                "$cond": {
                    "if": {"$lt": ["$term", {"$date": "2022-04-12T00:00:00Z"}]},
                    "then": "$grossTotal",
                    "else": 0
                }
            },
            "lateQuantity": {
                "$cond": {
                    "if": {"$lt": ["$term", {"$date": "2022-04-12T00:00:00Z"}]},
                    "then": 1,
                    "else": 0
                }
            }
        }
    }, 
    {
        "$group": {
            "_id": "$all",
            "quantity": {"$sum": 1},
            "grossTotal": {"$sum": "$grossTotal"},
            "lateQuantity": {"$sum": "$lateQuantity"},
            "lateGrossTotal": {"$sum": "$lateGrossTotal"},
        }
    }])
SELECT count(id), sum(grossTotal), sum(lateQuantity), sum(lateGrossTotal)
FROM (
    SELECT 
           id, 
           grossTotal,
           IF(date < ?, 1, 0) as lateQuantity
           IF(date < ?, grossTotal, 0) as lateGrossTotal
    FROM invoice
)

References

  1. https://www.mongodb.com/docs/manual/reference/sql-comparison/