MongoDB queries and aggregation pipeline for companies.json

This post will walk you through constructing MongoDB queries and using the aggregation pipeline to search through the documents in companies.json.

companies.json can be downloaded from through the link on GitHub. Take a look at the document structures to get an idea of its attributes before constructing MongoDB queries.

MongoDB practice queries

List only the first 20 names of companies founded after the year 2010, ordered alphabetically.

Use the mongoDb method find() to build a query for finding the first 20-names of companies founded after the year 2010 in alphabetical order.

Steps:

• Filter by year: "founded_year": {"$gt": 2010}
• Include company name in results: "name": 1}
• Sort in alphabetical order: sort({"name": 1}
• Limit results to first 20-entries matching criteria: limit(20)

Query:

db.research.find({"founded_year": {"$gt": 2010}}, {"name": 1}).sort({"name": 1}).limit(20)

Output

{ "_id" : ObjectId("52cdef7e4bab8bd67529b2f3"), "name" : "4shared" }
{ "_id" : ObjectId("52cdef7c4bab8bd6752982d4"), "name" : "Advaliant" }
{ "_id" : ObjectId("52cdef7d4bab8bd675299308"), "name" : "Advisor" }
...

List only the first 20 names of companies with offices in either California or Texas, ordered by the number of employees and sorted largest to smallest.

The components of this query are:

• {"offices.state_code": {"$in": ["CA", "TX"]}} to fetch all the state codes that are equal to “CA” or “TX”
• {"name": 1, "number_of_employees": 1} to include the company’s name and the number of employees in the results
• sort({"number_of_employees": -1} to sort in descending order
• limit(20) to limit results to 20-documents

Query:

db.research.find({"offices.state_code": {"$in": ["CA", "TX"]}}, {"name": 1, "number_of_employees": 1}).sort({"number_of_employees": -1}).limit(20)

Output

{ "_id" : ObjectId("52cdef7c4bab8bd675297e89"), "name" : "PayPal", "number_of_employees" : 300000 }
{ "_id" : ObjectId("52cdef7d4bab8bd675298aa4"), "name" : "Samsung Electronics", "number_of_employees" : 22726 }
{ "_id" : ObjectId("52cdef7d4bab8bd675298b99"), "name" : "Accenture", "number_of_employees" : 205000 }
{ "_id" : ObjectId("52cdef7e4bab8bd67529aa5"), "name" : "Flextronics International", "number_of_employees" : 200000 }
{ "_id" : ObjectId("52cdef7d4bab8bd67529956"), "name" : "Safeway", "number_of_employees" : 86000 }
...

Design and implement a MongoDB aggregation pipeline to show the total number of employees by state for all companies that have offices in the United States. 

The components of this query are:

• aggregate([]) to group documents together.
• {$unwind: "$offices"} to output a document for each element in the $offices array
• {$match: {"offices.country_code": "USA"}} to match US country codes
• {$group: {_id: "$offices.state_code", employees: {$sum: "$number_of_employees"}}} to group the documents by state code and sum of employess

Query:

db.research.aggregate([
    {$unwind: "$offices"},
    {$match: {"offices.country_code": "USA"}},
    {$group: {_id: "$offices.state_code", employees: {$sum: "$number_of_employees"}}}
])

Output

{ "_id" : "WV", "employees" : 5 }
{ "_id" : "AZ", "employees" : 548 }
{ "_id" : "NC", "employees" : 49802 }
{ "_id" : "NV", "employees" : 2705 }
{ "_id" : "PA", "employees" : 42780 }
{ "_id" : "OK", "employees" : 538 }
{ "_id" : "MN", "employees" : 8857 }
{ "_id" : "NH", "employees" : 464 }
{ "_id" : "VA", "employees" : 334707 }
...

Conclusion

Hopefully these instructions can get you started building different types of MongoDB queries. If you need more details on the methods used to develop the above queries, consult the MongoDB documentation.