Lecture 20: Mongo DB II¶

In [1]:
import json
import pymongo
import pprint
In [2]:
# instead of saving a full copy of existing data,
# just symlink to previous lecture. a bit hacky, but saves space!
!ln -sf ../lec18/data data
In [3]:
# this is a utility function we define so that find_all() prints nicely
def pretty_print(output_collection, n_to_print=3):
    """
    note if n_to_print is -1, this print everything
    """
    for i, doc in enumerate(output_collection):
        pprint.pprint(doc)        # nicely formats each document
        if i+1 == n_to_print: return

Aggregation Queries¶

Zips JSON from the MongoDB Aggregation Tutorial: https://www.mongodb.com/docs/manual/tutorial/aggregation-zip-code-data-set/

Load zips.json into new local.zips¶

For the sake of simplicity, we'll make a new collection zips in a new aggquerydb database.

In [4]:
client = pymongo.MongoClient('mongodb://localhost')
client.list_database_names()
Out[4]:
['admin', 'config', 'local', 'nobel_prizes']
In [5]:
# run this cell to make the new collection
# and insert zipcode documents

client.drop_database('aggquerydb') # if already exists
db = client.aggquerydb

with open('data/zips.json', encoding='utf-8') as f:
    for line in f.readlines():
        db.zips.insert_one(json.loads(line))
In [6]:
db.zips.count_documents({})
Out[6]:
29353
In [7]:
db.zips.find_one({})
Out[7]:
{'_id': '01001',
 'city': 'AGAWAM',
 'loc': [-72.622739, 42.070206],
 'pop': 15338,
 'state': 'MA'}

$group¶

In [8]:
output = db.zips.aggregate( [    
{ "$group": { "_id": "$state",  
              "totalPop":
                   { "$sum": "$pop" } } },    
{ "$match": { "totalPop":
            { "$gte": 15000000 } } }, 
{ "$sort" : { "totalPop" : -1 } }
] )

pretty_print(output)

{'_id': 'CA', 'totalPop': 29754890}
{'_id': 'NY', 'totalPop': 17990402}
{'_id': 'TX', 'totalPop': 16984601}

Aggregation Queries: Unwind/Lookup¶

Make a new collection, inventory.

In [9]:
db = client.aggquerydb          # stay in same database
db.drop_collection('inventory') # recreate as needed

db.inventory.insert_many( [    
{ "item": "journal",
 "tags": ["blank", "red"],
 "dim": [ 14, 21 ],
 "instock": [ { "loc": "A", "qty": 5 }, { "loc": "C", "qty": 15 } ]
},    
{ "item": "notebook",
 "tags": ["red", "blank"],
 "dim": [ 14, 21 ],
 "instock": [ { "loc": "C", "qty": 5 } ]
},    
{ "item": "paper",
 "tags": ["red", "blank", "plain"],
 "dim": [ 14, 21 ],
 "instock": [ { "loc": "A", "qty": 60 }, { "loc": "B", "qty": 15 } ]
},    
{ "item": "planner",
 "tags": ["blank", "red"],
 "dim": [ 22.85, 30 ] ,
 "instock": [ { "loc": "A", "qty": 40 }, { "loc": "B", "qty": 5 } ]
},    
{ "item": "postcard",
 "tags": ["blue"],
 "dim": [ 10, 15.25 ],
 "instock": [ { "loc": "E", "qty": 15 }, { "loc": "D", "qty": 35 } ]
}
]);

Unwind example¶

In [10]:
output = db.inventory.aggregate( [ 
{ "$unwind" : "$tags" }, 
{ "$project" : {"_id" : 0, "instock": 0}}  
] )

pretty_print(output, n_to_print=-1)

{'dim': [14, 21], 'item': 'journal', 'tags': 'blank'}
{'dim': [14, 21], 'item': 'journal', 'tags': 'red'}
{'dim': [14, 21], 'item': 'notebook', 'tags': 'red'}
{'dim': [14, 21], 'item': 'notebook', 'tags': 'blank'}
{'dim': [14, 21], 'item': 'paper', 'tags': 'red'}
{'dim': [14, 21], 'item': 'paper', 'tags': 'blank'}
{'dim': [14, 21], 'item': 'paper', 'tags': 'plain'}
{'dim': [22.85, 30], 'item': 'planner', 'tags': 'blank'}
{'dim': [22.85, 30], 'item': 'planner', 'tags': 'red'}
{'dim': [10, 15.25], 'item': 'postcard', 'tags': 'blue'}
In [11]:
output = db.inventory.aggregate( [ 
	{ "$unwind" : "$instock" },  
	{ "$group" : { "_id" : "$item", "totalqty" : {"$sum" : "$instock.qty"}}} 
] )

pretty_print(output, n_to_print=-1)

{'_id': 'notebook', 'totalqty': 5}
{'_id': 'paper', 'totalqty': 75}
{'_id': 'planner', 'totalqty': 45}
{'_id': 'postcard', 'totalqty': 50}
{'_id': 'journal', 'totalqty': 20}
In [12]:
output = db.inventory.aggregate( [ 
{ "$lookup" : {
    "from" : "inventory",
    "localField": "instock.loc", 
    "foreignField": "instock.loc", 
    "as":"otheritems"}
},  
{ "$project" :
    {"_id" : 0, "tags" : 0, "dim" : 0}
} ] )

pretty_print(output, n_to_print=1)

{'instock': [{'loc': 'A', 'qty': 5}, {'loc': 'C', 'qty': 15}],
 'item': 'journal',
 'otheritems': [{'_id': ObjectId('661428d51aeacf89f1eec0c4'),
                 'dim': [14, 21],
                 'instock': [{'loc': 'A', 'qty': 5}, {'loc': 'C', 'qty': 15}],
                 'item': 'journal',
                 'tags': ['blank', 'red']},
                {'_id': ObjectId('661428d51aeacf89f1eec0c5'),
                 'dim': [14, 21],
                 'instock': [{'loc': 'C', 'qty': 5}],
                 'item': 'notebook',
                 'tags': ['red', 'blank']},
                {'_id': ObjectId('661428d51aeacf89f1eec0c6'),
                 'dim': [14, 21],
                 'instock': [{'loc': 'A', 'qty': 60}, {'loc': 'B', 'qty': 15}],
                 'item': 'paper',
                 'tags': ['red', 'blank', 'plain']},
                {'_id': ObjectId('661428d51aeacf89f1eec0c7'),
                 'dim': [22.85, 30],
                 'instock': [{'loc': 'A', 'qty': 40}, {'loc': 'B', 'qty': 5}],
                 'item': 'planner',
                 'tags': ['blank', 'red']}]}
In [13]:
output = db.inventory.aggregate( [ 
{ "$lookup" : {
    "from" : "inventory",
    "localField": "instock.loc", 
    "foreignField": "instock.loc", 
    "as":"otheritems"}
},  
{ "$project" :
    {"_id" : 0, "tags" : 0, "dim" : 0, "otheritems._id": 0}
} ] )

pretty_print(output, n_to_print=1)

{'instock': [{'loc': 'A', 'qty': 5}, {'loc': 'C', 'qty': 15}],
 'item': 'journal',
 'otheritems': [{'dim': [14, 21],
                 'instock': [{'loc': 'A', 'qty': 5}, {'loc': 'C', 'qty': 15}],
                 'item': 'journal',
                 'tags': ['blank', 'red']},
                {'dim': [14, 21],
                 'instock': [{'loc': 'C', 'qty': 5}],
                 'item': 'notebook',
                 'tags': ['red', 'blank']},
                {'dim': [14, 21],
                 'instock': [{'loc': 'A', 'qty': 60}, {'loc': 'B', 'qty': 15}],
                 'item': 'paper',
                 'tags': ['red', 'blank', 'plain']},
                {'dim': [22.85, 30],
                 'instock': [{'loc': 'A', 'qty': 40}, {'loc': 'B', 'qty': 5}],
                 'item': 'planner',
                 'tags': ['blank', 'red']}]}

Multiple Attribute Grouping¶

In [14]:
# reimport/redefine as needed
import json
import pymongo
import pprint

def pretty_print(output_collection, n_to_print=3):
    """
    note if n_to_print is -1, this print everything
    """
    for i, doc in enumerate(output_collection):
        pprint.pprint(doc)        # nicely formats each document
        if i+1 == n_to_print: return
In [15]:
client = pymongo.MongoClient('mongodb://localhost')
client.list_database_names()
Out[15]:
['admin', 'aggquerydb', 'config', 'local', 'nobel_prizes']
In [16]:
db = client.aggquerydb
In [17]:
db.zips.count_documents({})
Out[17]:
29353
  1. What is this doing?
In [18]:
output = db.zips.aggregate( [    
    { "$group": { "_id": { "state": "$state", "city": "$city" },
                 "pop": { "$sum": "$pop" } } 
    },    
    { "$group": { "_id": "$_id.state", 
                 "avgCityPop": { "$avg": "$pop" } } 
    } 
] )
pretty_print(output, n_to_print=10)

{'_id': 'TN', 'avgCityPop': 9656.350495049504}
{'_id': 'NC', 'avgCityPop': 10622.815705128205}
{'_id': 'KY', 'avgCityPop': 4767.164721141375}
{'_id': 'UT', 'avgCityPop': 9518.508287292818}
{'_id': 'IL', 'avgCityPop': 9954.334494773519}
{'_id': 'MA', 'avgCityPop': 14855.37037037037}
{'_id': 'DC', 'avgCityPop': 303450.0}
{'_id': 'TX', 'avgCityPop': 13775.02108678021}
{'_id': 'GA', 'avgCityPop': 11547.62210338681}
{'_id': 'WI', 'avgCityPop': 7323.00748502994}
  1. What is this doing?
In [19]:
output = db.zips.aggregate( [ 
{ "$group": { "_id": { "state": "$state", "city": "$city" }, 
             "pop": { "$sum": "$pop" } } }, 
{ "$sort": { "pop": -1 } }, 
{ "$group": { "_id" : "$_id.state",
             "bigCity": { "$first": "$_id.city" }, 
             "bigPop": { "$first": "$pop" } } }, 
{ "$sort" : {"bigPop" : -1} },
{ "$project" : {"bigPop" : 0} }
] )

pretty_print(output, n_to_print=10)

{'_id': 'IL', 'bigCity': 'CHICAGO'}
{'_id': 'NY', 'bigCity': 'BROOKLYN'}
{'_id': 'CA', 'bigCity': 'LOS ANGELES'}
{'_id': 'TX', 'bigCity': 'HOUSTON'}
{'_id': 'PA', 'bigCity': 'PHILADELPHIA'}
{'_id': 'MI', 'bigCity': 'DETROIT'}
{'_id': 'AZ', 'bigCity': 'PHOENIX'}
{'_id': 'FL', 'bigCity': 'MIAMI'}
{'_id': 'MD', 'bigCity': 'BALTIMORE'}
{'_id': 'TN', 'bigCity': 'MEMPHIS'}
  1. What is this doing?
In [20]:
output = db.zips.aggregate( [ 
{ "$group": { "_id": { "state": "$state", "city": "$city" }, 
             "pop": { "$sum": "$pop" } } }, 
{ "$sort": { "pop": -1 } }, 
{ "$group": { "_id" : "$_id.state", 
             "bigCity": { "$first": "$_id.city" }, 
             "bigPop": { "$first": "$pop" } } }, 
{ "$sort" : {"bigPop" : -1} },
{ "$project" : { "_id" : 0, "state" : "$_id", 
                "bigCityDeets": { "name": "$bigCity", "pop": "$bigPop" } } }
] )

pretty_print(output, n_to_print=10)

{'bigCityDeets': {'name': 'CHICAGO', 'pop': 2452177}, 'state': 'IL'}
{'bigCityDeets': {'name': 'BROOKLYN', 'pop': 2300504}, 'state': 'NY'}
{'bigCityDeets': {'name': 'LOS ANGELES', 'pop': 2102295}, 'state': 'CA'}
{'bigCityDeets': {'name': 'HOUSTON', 'pop': 2095918}, 'state': 'TX'}
{'bigCityDeets': {'name': 'PHILADELPHIA', 'pop': 1610956}, 'state': 'PA'}
{'bigCityDeets': {'name': 'DETROIT', 'pop': 963243}, 'state': 'MI'}
{'bigCityDeets': {'name': 'PHOENIX', 'pop': 890853}, 'state': 'AZ'}
{'bigCityDeets': {'name': 'MIAMI', 'pop': 825232}, 'state': 'FL'}
{'bigCityDeets': {'name': 'BALTIMORE', 'pop': 733081}, 'state': 'MD'}
{'bigCityDeets': {'name': 'MEMPHIS', 'pop': 632837}, 'state': 'TN'}

[Extra] Aggregation Pipeline Demos¶

Nobel laureate data from Kaggle: https://www.kaggle.com/datasets/imdevskp/nobel-prize/data

In [ ]:
client = pymongo.MongoClient('mongodb://localhost')
client.list_database_names()

client.drop_database('nobel_prizes') # if already exists

db = client.nobel_prizes
# run this cell to insert into the collection prizes
with open('data/prize.json', encoding='utf-8') as f:
    db.prizes.insert_many(json.loads(f.read()))

print("databases", client.list_database_names())

db = client.nobel_prizes
print("collections in nobel_prizes database", db.list_collection_names())
In [ ]:
# A
output = db.prizes.aggregate([{"$group": {"_id": "$category",
                                          "awardyears": {"$sum" : 1}}}])
pretty_print(output, n_to_print=-1)
In [ ]:
# B
output = db.prizes.aggregate([{"$group": {"_id": "$category",
                                          "awardyears": {"$sum" : 1}}},
                              {"$match" : {"awardyears": {"$lt": 100}}}])
pretty_print(output, n_to_print=-1)
In [ ]:
# C
output = db.prizes.aggregate([{"$group": {"_id": "$category", 
                                 "awardyears": {"$sum" : 1}}}, 
                     {"$match" : {"awardyears": {"$lt": 100}}}, 
                     {"$project" : {"_id": 0, "awardyears": 1}}])
pretty_print(output, n_to_print=-1)
In [ ]:
# D
output = db.prizes.aggregate([{"$unwind": "$laureates"},
                              {"$group": {"_id": "$category", 
                                          "awards": {"$sum" : 1}}}]) 
pretty_print(output, n_to_print=-1)
In [ ]:
# E
output = db.prizes.aggregate([{"$unwind": "$laureates"}, 
                              {"$group": {"_id": {"category": "$category",
                                                  "year": "$year"}, 
                                          "awards": {"$sum" : 1}}}])
pretty_print(output, n_to_print=10)
In [ ]:
# F
output = db.prizes.aggregate([{"$unwind": "$laureates"}, 
                              {"$group": {"_id": {"category": "$category", 
                                                  "year": "$year"},
                                          "awards": {"$sum" : 1}}},
                              {"$sort" : {"awards": -1}}])
pretty_print(output, n_to_print=15)
In [ ]:
# G
output = db.prizes.aggregate([{"$unwind": "$laureates"}, 
                              {"$group": {"_id": {"category": "$category",
                                                  "year": "$year"}, 
                                          "awards": {"$sum" : 1}}},
                              {"$group": {"_id":"$_id.category",
                                          "avgawards": {"$avg" : "$awards"}}}])
pretty_print(output, n_to_print=-1)