SQL/Description MongoDB

Select

SELECT *
db.people.find()
FROM people

SELECT id, db.people.find(

user_id, { },
status { user_id: 1, status: 1 }
FROM people )

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

Condition

SELECT * db.people.find(
FROM inventory { status: "D" }
WHERE status = "D" )

SELECT * db.people.find(
FROM people { status: { $ne: "A" } }
WHERE status != "A" )

SELECT * db.people.find(
FROM inventory { status: { $in: [ "A", "D" ] } }
WHERE status in ("A", "D") )

SELECT * db.people.find(
FROM inventory { status: "A", qty: { $lt: 30 } }
WHERE status = "A" AND qty < 30 )

SELECT * db.people.find(
FROM inventory { $or: [ { status: "A" }, { qty: { $lt: 30 } } ] }
WHERE status = "A" OR qty < 30 )

db.people.find(
SELECT *
{ status: "A", $or: [ { qty: { $lt: 30 } }, { item: /^p/ }
FROM inventory
] }
WHERE status = "A" AND ( qty < 30 OR item LIKE "p%")
)
SELECT *
FROM people
db.people.find( { status: "A" } ).sort( { user_id: 1 } )
WHERE status = "A"
ORDER BY user_id ASC

db.people.count()
SELECT COUNT(*)
or
FROM people
db.people.find().count()

db.people.count( { user_id: { $exists: true } } )

SELECT COUNT(user_id)
or
FROM people
db.people.find( { user_id: { $exists: true } } ).count()

SELECT COUNT(*) db.people.count( { age: { $gt: 30 } } )

FROM people or
WHERE age > 30 db.people.find( { age: { $gt: 30 } } ).count()

db.people.aggregate( [ { $group : { _id : "$status" } } ] )

SELECT DISTINCT(status)
or
FROM people
db.people.distinct( "status" )

SELECT * db.people.findOne()
FROM people or
LIMIT 1 db.people.find().limit(1)

SELECT *
FROM people
db.people.find().limit(5).skip(10)
LIMIT 5
SKIP 10

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

Project

const cursor = db
.collection('inventory')
SELECT _id, item, status
.find({
FROM inventory
status: 'A'
WHERE status = "A"
})
.project({ item: 1, status: 1 });

const cursor = db
Return All But the Excluded Fields
.collection('inventory')
 .find({
status: 'A'
})
.project({ status: 0, instock: 0 });

const cursor = db
.collection('inventory')
.find({
Return Specific Fields in Embedded Documents
status: 'A'
})
.project({ item: 1, status: 1, 'size.uom': 1 });

Aggregation

The $match stage: db.orders.aggregate( [

● Filters the pizza order documents to pizzas with a size {
of medium. $match: { size: "medium" }
● Passes the remaining documents to the $group stage. },
The $group stage: {
● Groups the remaining documents by pizza name. $group: { _id: "$name", totalQuantity: { $sum:
● Uses $sum to calculate the total order quantity for each "$quantity" } }
pizza name. The total is stored in the totalQuantity }
field returned by the aggregation pipeline. ] )

db.orders.aggregate( [
{
$match:
The $match stage: {
● Filters the pizza order documents to those in a date "date": { $gte: new ISODate( "2020-01-30" ), $lt: new
range specified using $gte and $lt. ISODate( "2022-01-30" ) }
● Passes the remaining documents to the $group stage. }
The $group stage: },
● Groups the documents by date using $dateToString. {
● For each group, calculates: $group:
○ Total order value using $sum and $multiply. {
○ Average order quantity using $avg. _id: { $dateToString: { format: "%Y-%m-%d", date:
● Passes the grouped documents to the $sort stage. "$date" } },
The $sort stage: totalOrderValue: { $sum: { $multiply: [ "$price",
● Sorts the documents by the total order value for each "$quantity" ] } },
group in descending order (-1). averageOrderQuantity: { $avg: "$quantity" }
● Returns the sorted documents. }
},
{
$sort: { totalOrderValue: -1 }
 }

] )