demonstrating advanced query operations

Question

Overview

We will be demonstrating advanced query operations for this assignment. Advanced queries will let you analyze data in a more programmatic fashion at the database layer. Since the key-value structure of an NoSQL database closely resembles the object-oriented data structures of many programming languages, this likeness will enhance the ease and portability of MongoDB queries. The aggregation pipeline facility of MongoDB gives us a set of operators to enable limited set theory operations (grouping, intersections, and so on) for documents within the same collection.

Prompt

After completing the textbook reading and reviewing the module resources, complete the following tasks using the MongoDB shell.

1. Using the mongoimport tool, create the database “companies” by loading the documents found in the “companies.json” file into the “research” collection. This file is located in the “/usr/local/datasets/” directory in Apporto. Verify your load by issuing the following queries:
2. db.research.find({"name" : "AdventNet"})
3. db.research.find({"founded_year" : 1996},{"name" : 1}).limit(10)
4. Provide screenshots of your statements and the results as evidence.
5. Perform the following tasks using MongoDB queries:
6. List only the first 20 names of companies founded after the year 2010, ordered alphabetically.
7. 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.
8. Provide screenshots of your statements and the results as evidence.
9. Design and implement a MongoDB aggregation pipeline to show the total number of offices by state for all companies that have offices in the United States. Be sure that you account for the fact that some companies have offices in several states. Explain your aggregation pipeline.

The assignment must be submitted as a Microsoft Word document (or equivalent) with the appropriate screenshots.

Answer 1

Hey Femi! Here is a proposed solution to your prompt:

Using mongoimport to create the database and load documents:

mongoimport --db companies --collection research --file /usr/local/datasets/companies.json

This command imports the documents from "companies.json" into the "research" collection within the "companies" database.

Verifying the load: Execute the following queries in the MongoDB shell:

db.research.find({"name": "AdventNet"})

db.research.find({"founded_year": 1996}, {"name": 1}).limit(10)

Take screenshots of the queries and their results as evidence.

Performing tasks using MongoDB queries:

1. List the first 20 names of companies founded after 2010, ordered alphabetically:

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

1. List the first 20 names of companies with offices in California or Texas, ordered by the number of employees (largest to smallest):

db.research.find({"$or": [{"offices.state": "CA"}, {"offices.state": "TX"}]}, {"name": 1})

.sort({"number_of_employees": -1}).limit(20)

Take screenshots of the queries and their results.

Designing and implementing an aggregation pipeline:

db.research.aggregate([

{$unwind: "$offices"},

{$match: {"offices.country": "United States"}},

{$group: {_id: "$offices.state", total_offices: {$sum: 1}}}

])

This aggregation pipeline:

1. Unwinds the "offices" array to have one document per office.
2. Matches only offices in the United States.
3. Groups the documents by state and calculates the total number of offices in each state.

Take screenshots of the aggregation pipeline and its results.

Finally, you can compile the screenshots and explanations into a Microsoft Word document (or equivalent) as specified in the assignment requirements.

I hope this helps!