Sure! Here are a few examples of MongoDB queries involving `customer` and `order` collections.
Let's
assume we have the following collections:
### Customers Collection
```json
"_id": 1,
"name": "Alice",
"email": "alice@example.com"
},
"_id": 2,
"name": "Bob",
"email": "bob@example.com"
},
"_id": 3,
"name": "Charlie",
"email": "charlie@example.com"
```
### Orders Collection
```json
[
"_id": 101,
"customer_id": 1,
"product": "Laptop",
"amount": 1200
},
"_id": 102,
"customer_id": 2,
"product": "Phone",
"amount": 800
},
"_id": 103,
"customer_id": 1,
"product": "Tablet",
"amount": 300
```
### Example Queries
#### 1. Find all orders made by a specific customer (e.g., customer with `_id` 1):
```javascript
db.orders.find({ customer_id: 1 })
```
#### 2. Find customer information along with their orders using an aggregation pipeline:
```javascript
db.customers.aggregate([
$lookup: {
from: "orders",
localField: "_id",
foreignField: "customer_id",
as: "orders"
])
```
#### 3. Find customers who have spent more than $1000 in total:
```javascript
db.orders.aggregate([
$group: {
_id: "$customer_id",
totalSpent: { $sum: "$amount" }
}
},
$match: {
totalSpent: { $gt: 1000 }
},
$lookup: {
from: "customers",
localField: "_id",
foreignField: "_id",
as: "customer"
},
$unwind: "$customer"
},
$project: {
_id: 0,
name: "$customer.name",
email: "$customer.email",
totalSpent: 1
}
}
])
```
#### 4. Find the total number of orders placed by each customer:
```javascript
db.orders.aggregate([
$group: {
_id: "$customer_id",
orderCount: { $sum: 1 }
},
$lookup: {
from: "customers",
localField: "_id",
foreignField: "_id",
as: "customer"
},
$unwind: "$customer"
},
{
$project: {
_id: 0,
name: "$customer.name",
email: "$customer.email",
orderCount: 1
])
```
#### 5. Find all customers who have not placed any orders:
```javascript
db.customers.aggregate([
$lookup: {
from: "orders",
localField: "_id",
foreignField: "customer_id",
as: "orders"
},
$match: {
orders: { $eq: [] }
}
},
$project: {
_id: 0,
name: 1,
email: 1
])
```
These queries demonstrate how to interact with `customer` and `order` collections in MongoDB,
performing lookups, aggregations, and filtering to obtain meaningful information from the data.
Sure! Here's an exercise involving MongoDB queries. Let's assume you have the following two
collections: `customers` and `orders`.
### Customers Collection
```json
{ "_id": 1, "name": "Alice", "email": "alice@example.com" },
{ "_id": 2, "name": "Bob", "email": "bob@example.com" },
{ "_id": 3, "name": "Charlie", "email": "charlie@example.com" }
```
### Orders Collection
```json
{ "_id": 101, "customer_id": 1, "product": "Laptop", "amount": 1200, "date": "2024-01-01" },
{ "_id": 102, "customer_id": 2, "product": "Phone", "amount": 800, "date": "2024-01-15" },
{ "_id": 103, "customer_id": 1, "product": "Tablet", "amount": 300, "date": "2024-02-20" },
{ "_id": 104, "customer_id": 3, "product": "Monitor", "amount": 150, "date": "2024-02-25" },
{ "_id": 105, "customer_id": 2, "product": "Keyboard", "amount": 100, "date": "2024-03-10" }
```
### Exercise Questions
1. **Find all orders placed by customer "Alice".**
2. **Calculate the total amount spent by each customer.**
3. **List all customers who have placed at least one order.**
4. **Find the most recent order for each customer.**
5. **Identify customers who have spent more than $1000.**
6. **List all customers and include their orders (use aggregation).**
7. **Find all customers who have not placed any orders.**
### Solutions
#### 1. Find all orders placed by customer "Alice":
```javascript
const alice = db.customers.findOne({ name: "Alice" });
db.orders.find({ customer_id: alice._id });
```
#### 2. Calculate the total amount spent by each customer:
```javascript
db.orders.aggregate([
$group: {
_id: "$customer_id",
totalSpent: { $sum: "$amount" }
},
$lookup: {
from: "customers",
localField: "_id",
foreignField: "_id",
as: "customer"
},
$unwind: "$customer"
},
{
$project: {
_id: 0,
name: "$customer.name",
email: "$customer.email",
totalSpent: 1
]);
```
#### 3. List all customers who have placed at least one order:
```javascript
db.orders.distinct("customer_id").forEach(customer_id => {
printjson(db.customers.findOne({ _id: customer_id }));
});
```
#### 4. Find the most recent order for each customer:
```javascript
db.orders.aggregate([
$sort: { date: -1 }
},
$group: {
_id: "$customer_id",
mostRecentOrder: { $first: "$$ROOT" }
},
$lookup: {
from: "customers",
localField: "_id",
foreignField: "_id",
as: "customer"
},
$unwind: "$customer"
},
$project: {
_id: 0,
name: "$customer.name",
email: "$customer.email",
mostRecentOrder: 1
]);
```
#### 5. Identify customers who have spent more than $1000:
```javascript
db.orders.aggregate([
$group: {
_id: "$customer_id",
totalSpent: { $sum: "$amount" }
},
$match: {
totalSpent: { $gt: 1000 }
},
$lookup: {
from: "customers",
localField: "_id",
foreignField: "_id",
as: "customer"
},
$unwind: "$customer"
},
$project: {
_id: 0,
name: "$customer.name",
email: "$customer.email",
totalSpent: 1
]);
```
#### 6. List all customers and include their orders (use aggregation):
```javascript
db.customers.aggregate([
$lookup: {
from: "orders",
localField: "_id",
foreignField: "customer_id",
as: "orders"
]);
```
#### 7. Find all customers who have not placed any orders:
```javascript
db.customers.aggregate([
$lookup: {
from: "orders",
localField: "_id",
foreignField: "customer_id",
as: "orders"
},
$match: {
orders: { $eq: [] }
},
$project: {
_id: 0,
name: 1,
email: 1
]);
```
These queries cover a range of operations, including basic retrieval, aggregation, and data joining. They
should provide a good exercise in working with MongoDB queries.
