Question

Task 2 (5.0 marks)
Retrieving information from BSON documents
Start mongo client and connect to the MongoDB database server. Next, process a script file customerOrder2.js to insert BSON documents into a collection customerOrder. Make yourself familiar with the contents of the collection.
a) Find all customer orders' detail by customer from Singapore who have ever made order after 1 April 2020.
b) Find the first name, last name, emails, and address of customers from Singapore who have ever bought a personal computer.
c) Find the first name, last name and email of customer who have no telephone.
d) Find the first name, last name, date of birth (DOB), and the language of customers who speak both English and Mandarin.
e) Find the first name, last name, address, and balance of customer whose balance is in a range between 1000 and 2500.
f) Find the first name, last name, and email of customer who bought MicroSD. Do not list the object id of the customer.

db.customerOrder.remove({});

db.customerOrder.insert (
{
"firstName": "Andrew",
"lastName": "Lee",
"DOB": ISODate("1974-10-28T00:00:00Z"),
"phone": "+1 (959) 567-3312",
"email": "[email protected]",
"address": {
"street": "Cornish Street, Victoria",
"houseNumber": "68",
"postalCode": "3024",
"country": "Australia"
},
"language": ["English", "Mandarin"],
"balance": 0,
"orders": [
{
"orderNumber": "ord003",
"orderDate": ISODate("2020-01-10T00:00:00Z"),
"staffNumber": "stf789",
"lineItem": [
{
"lineNum": 1,
"productCode": "p001",
"productDesc": "Note-book",
"orderQty": 2,
"price": {
"currency": "SGD",
"value": 4800
}
},
{
"lineNum": 2,
"productCode": "s005",
"productDesc": "256GB MicroSD",
"orderQty": 1,
"price": {
"currency": "USD",
"value": 90
}
},
{
"lineNum": 3,
"productCode": "a001",
"productDesc": "ear-piece",
"orderQty": 1,
"price": {
"currency": "SGD",
"value": 10
}
}
]
},
{
"orderNumber": "ord003",
"orderDate": ISODate("2020-03-17T00:00:00Z"),
"staffNumber": "stf444",
"lineItem": [
{
"lineNum": 1,
"productCode": "p001",
"productDesc": "Note-book",
"orderQty": 2,
"price": {
"currency": "SGD",
"value": 4800
}
},
{
"lineNum": 2,
"productCode": "s005",
"productDesc": "4TB Hard-disk",
"orderQty": 1,
"price": {
"currency": "USD",
"value": 350
}
},
{
"lineNum": 3,
"productCode": "a001",
"productDesc": "ear-piece",
"orderQty": 1,
"price": {
"currency": "SGD",
"value": 10
}
}
]
},
{
"orderNumber": "ord005",
"orderDate": ISODate("2020-02-22T00:00:00Z"),
"staffNumber": "stf890",
"lineItem": [
{
"lineNum": 1,
"productCode": "p002",
"productDesc": "Personal Computer",
"orderQty": 1,
"price": {
"currency": "SGD",
"value": 1400
}
},
{
"lineNum": 2,
"productCode": "a005",
"productDesc": "Laser Printer",
"orderQty": 1,
"price": {
"currency": "USD",
"value": 250
}
}
]
}
]
}
);

db.customerOrder.insert (
{
"firstName": "Andrew",
"lastName": "Smith",
"DOB": ISODate("1970-05-10T00:00:00Z"),
"email": "[email protected]",
"address": {
"street": "Lafayette Lane West Lafayette",
"houseNumber": "734",
"postalCode": "47906",
"country": "USA"
},
"language": ["English"],
"balance": 2600,
"orders": [
{
"orderNumber": "ord001",
"orderDate": ISODate("2020-04-23T00:00:00Z"),
"staffNumber": "stf123",
"lineItem": [
{
"lineNum": 1,
"productCode": "p001",
"productDesc": "Note-book",
"orderQty": 2,
"price": {
"currency": "SGD",
"value": 4800
}
},
{
"lineNum": 2,
"productCode": "s005",
"productDesc": "2TB Hard-disk",
"orderQty": 1,
"price": {
"currency": "USD",
"value": 150
}
},
{
"lineNum": 3,
"productCode": "a001",
"productDesc": "ear-piece",
"orderQty": 1,
"price": {
"currency": "SGD",
"value": 10
}
}
]
},
{
"orderNumber": "ord005",
"orderDate": ISODate("2020-04-16T00:00:00Z"),
"staffNumber": "stf444",
"lineItem": [
{
"lineNum": 1,
"productCode": "p002",
"productDesc": "Personal Computer",
"orderQty": 1,
"price": {
"currency": "SGD",
"value": 1400
}
},
{
"lineNum": 2,
"productCode": "a005",
"productDesc": "Laser Printer",
"orderQty": 1,
"price": {
"currency": "USD",
"value": 250
}
}
]
}
]
}
);

Answer 1

Here Collection name is customerOrder and i have written the queries according the question.

Solution of Question a :

db.customerOrder.find( { country : "Singapore ", orderDate : { gt:"2020-04-01" } } ,{order})

Solution of Question b :

db.customerOrder.find( { country : "Singapore ", productDesc : "Personal Computer" } , {firstName,lastName,email,address})

Solution of Question c : db.customerOrder.find( { phone: : " " } , {firstName,lastName,email})

Solution of Question d : db.customerOrder.find( { language: : {"English", "Mandarin"} } , {firstName,lastName,DOB,language})

Solution of Question e: db.customerOrder.find( { balance: : {"$gte: 1000, $lte: 2500"} } , {firstName,lastName,address,balance})

Solution of Question f : db.customerOrder.find( { productDesc: : " $regex: /MicroSD$/" } , {firstName,lastName,email})