Managing inventory for an office supply store

Imagine running an office supply company. We need to keep track of customer information, sales data, and store location.

Sales data query

We may ask these questions related to sales data:

• Which store is the most popular?
• Which payment method is the most popular?

To answer these questions, we need to learn a few more concepts that extend the functions of previously learned CRUD methods.

Projection

As the documents are getting longer, we may want to only select specific fields to be returned. Projection is used exactly for that.

A projection is simply a BSON object specifying the fields that are included or excluded. It can be supplied at the end of find() or findOne() query methods.

For example, to only select the fields saleDate, and storeLocation:

> db.sales.findOne({}, {
...     saleDate: 1,
...     storeLocation: 1
... })
{
    "_id" : ObjectId("5bd761dcae323e45a93ccfe8"),
    "saleDate" : ISODate("2015-03-23T21:06:49.506Z"),
    "storeLocation" : "Denver"
}

Select the fields saleDate and storeLocation, but without _id:

> db.sales.findOne({}, {
...     _id: 0,
...     saleDate: 1,
...     storeLocation: 1
... })
{
    "saleDate" : ISODate("2015-03-23T21:06:49.506Z"),
    "storeLocation" : "Denver"
}

Select all the fields except for items and customer:

> db.sales.findOne({}, {
...     items: 0,
...     customer: 0,
... })
{
    "_id" : ObjectId("5bd761dcae323e45a93ccfe8"),
    "saleDate" : ISODate("2015-03-23T21:06:49.506Z"),
    "storeLocation" : "Denver",
    "couponUsed" : true,
    "purchaseMethod" : "Online"
}

Projection will be used very frequently for a cleaner output.

Cursor methods

Cursor methods are used in conjunction with a query method, such as find(), to perform further actions such as sorting, counting, filtering and formatting. Cursor methods can be chained together to call multiple cursor methods at the same time.

limit() method

limit() method limits the number of documents to be returned.

Find the first 2 documents in sales collection:

> db.sales.find({}, { items: 0 }).limit(2)
{ "_id" : ObjectId("5bd761dcae323e45a93ccfe8"), "saleDate" : ISODate("2015-03-23T21:06:49.506Z"), "storeLocation" : "Denver", "customer" : { "gender" : "M", "age" : 42, "email" : "[email protected]", "satisfaction" : 4 }, "couponUsed" : true, "purchaseMethod" : "Online" }
{ "_id" : ObjectId("5bd761dcae323e45a93ccfe9"), "saleDate" : ISODate("2015-08-25T10:01:02.918Z"), "storeLocation" : "Seattle", "customer" : { "gender" : "M", "age" : 50, "email" : "[email protected]", "satisfaction" : 5 }, "couponUsed" : false, "purchaseMethod" : "Phone" }

You can see that the output is not very human readable. We can use the pretty() cursor method to format the output.

pretty() method

Format output to be more human readable:

> db.sales.find({}, { items: 0 }).limit(2).pretty()
{
    "_id" : ObjectId("5bd761dcae323e45a93ccfe8"),
    "saleDate" : ISODate("2015-03-23T21:06:49.506Z"),
    "storeLocation" : "Denver",
    "customer" : {
        "gender" : "M",
        "age" : 42,
        "email" : "[email protected]",
        "satisfaction" : 4
    },
    "couponUsed" : true,
    "purchaseMethod" : "Online"
}
{
    "_id" : ObjectId("5bd761dcae323e45a93ccfe9"),
    "saleDate" : ISODate("2015-08-25T10:01:02.918Z"),
    "storeLocation" : "Seattle",
    "customer" : {
        "gender" : "M",
        "age" : 50,
        "email" : "[email protected]",
        "satisfaction" : 5
    },
    "couponUsed" : false,
    "purchaseMethod" : "Phone"
}

// Contents of *items* are ignored by projection.

sort() method

sort() specifies the order of matching documents to be returned. It is usually used together with another query method, such as find().

It has the following syntax:
db.collection.find().sort({ field: value })

field is the field name to be sorted. value is a value of 1 or -1, representing ascending or descending sort respectively.

Find the last 3 sales record, sorted by sales date:

> db.sales.find({}, { items: 0 }).sort({ saleDate: -1 }).limit(3)
{ "_id" : ObjectId("5bd761deae323e45a93cdcb3"), "saleDate" : ISODate("2017-12-31T18:15:34.758Z"), "storeLocation" : "Austin", "customer" : { "gender" : "M", "age" : 33, "email" : "[email protected]", "satisfaction" : 1 }, "couponUsed" : false, "purchaseMethod" : "In store" }
{ "_id" : ObjectId("5bd761deae323e45a93cdd61"), "saleDate" : ISODate("2017-12-31T16:11:17.768Z"), "storeLocation" : "Seattle", "customer" : { "gender" : "M", "age" : 39, "email" : "[email protected]", "satisfaction" : 5 }, "couponUsed" : false, "purchaseMethod" : "Online" }
{ "_id" : ObjectId("5bd761ddae323e45a93cd42b"), "saleDate" : ISODate("2017-12-31T15:21:11.919Z"), "storeLocation" : "Denver", "customer" : { "gender" : "M", "age" : 39, "email" : "[email protected]", "satisfaction" : 4 }, "couponUsed" : false, "purchaseMethod" : "In store" }

Finding the answers

Most popular store

The most popular store is defined by the most number of transactions that happened.

> db.sales.count({ storeLocation: "Austin" })
676
> db.sales.count({ storeLocation: "Denver" })
1549
> db.sales.count({ storeLocation: "London" })
794
> db.sales.count({ storeLocation: "New York" })
501
> db.sales.count({ storeLocation: "San Diego" })
346
> db.sales.count({ storeLocation: "Seattle" })
1134

We can see that Denver was the busiest, with 1549 transactions.

Most popular payment method

> db.sales.count({ purchaseMethod: "In store" })
2819
> db.sales.count({ purchaseMethod: "Online" })
1585
> db.sales.count({ purchaseMethod: "Phone" })
596

Most people paid in-store, while online payment is the second most popular payment method.

What is next?

Some questions that cannot yet be answered efficiently with existing knowledge:

• How much revenue did we make in a year?
• Which item is the most/least popular?
• Which item made the most/least revenue?
• Which item category made the most/least revenue?
• Which store has the highest/lowest average satisfaction score?
• Is there any notable pattern in customer group?

These questions can be answered using aggregation methods, which can create summaries of data.