<jupyter_start><jupyter_text>MongoDBMongoDB is a document database. It stores JSON objects.- [Documentation](https://docs.mongodb.com)- [Query selectors](https://docs.mongodb.com/manual/reference/operator/query/query-selectors)Note that MongoDB also provides a GUI interface via [MongDB Compasss](https://www.mongodb.com/products/compass) that might be useful when you are getting familiar with MongoDB. However, we will focus only on `pymongo`.  Concepts- What a document database is- Why document databases- Collections ~ tables- Documents ~ rows- Joins are possible but more common to embed nested objects- [Basic data manipulation: CRUD](https://docs.mongodb.com/manual/crud/)- Using `find`- Simple summaries- Using the `aggregate` method and setting up pipelines- Geospatial queries- Creating indexes to speed up queries<jupyter_code>from pymongo import MongoClient, GEOSPHERE
from bson.objectid import ObjectId
from bson.son import SON
import requests
from bson import json_util
import collections
from pathlib import Path
import os
from pprint import pprint<jupyter_output><empty_output><jupyter_text>Set up This connects to the MongoDB daemon<jupyter_code>client = MongoClient('mongodb:27017')<jupyter_output><empty_output><jupyter_text>This specifies the database. It does not matter if it does not exist.<jupyter_code>client.drop_database('starwars')
db = client.starwars<jupyter_output><empty_output><jupyter_text>This specifies a `collection`<jupyter_code>people = db.people<jupyter_output><empty_output><jupyter_text>Check what collections are in the database. Note that the `people` collection is only created when the first value is inserted.<jupyter_code>db.list_collection_names()<jupyter_output><empty_output><jupyter_text>Get Data<jupyter_code>base_url = 'http://swapi.dev/api/'
resp = requests.get(os.path.join(base_url, 'people/1'))
data = resp.json()
data<jupyter_output><empty_output><jupyter_text>We will fetch details of the homeworld and starships as a nested document.<jupyter_code>def get_nested(d):
    d['homeworld']  = requests.get(d['homeworld']).json()
    urls = d['starships']
    starships = [requests.get(url).json() for url in urls]
    d['starships']  = starships
    return d<jupyter_output><empty_output><jupyter_text>We need to convert numbers from strings returned by the REST API<jupyter_code>def convert_str(x):
    try:
        return int(x)
    except:
        return x

def to_num(data):
    for key in data:
        val = data[key]
        if isinstance(val, str):
            data[key] = convert_str(val)
        elif isinstance(val, dict):
            for k, v in val.items():
                if isinstance(v, str):
                    val[k] = convert_str(v)
        elif isinstance(val, list):
            for i, item in enumerate(val):
                if isinstance(item, str):
                    data[key][i] = convert_str(item)
                elif isinstance(item, dict):
                    for k, v in item.items():
                        if isinstance(v, str):
                            data[key][i][k] = convert_str(v)      
    return data
data = to_num(get_nested(data))
data<jupyter_output><empty_output><jupyter_text>Insertion  Single inserts<jupyter_code>result = people.insert_one(data)
db.list_collection_names()<jupyter_output><empty_output><jupyter_text>Bulk inserts We load some previously retrieved values from file to avoid hitting the SWAPI server repeatedly.<jupyter_code>import pickle

with open('sw.pickle', 'rb') as f:
    xs = pickle.load(f)
result = people.insert_many(xs)
result.inserted_ids<jupyter_output><empty_output><jupyter_text>Queries<jupyter_code>people.find_one(
    # search criteria
    {'name': '<NAME>'}, 
    # values to return
    {'name': True, 
     'hair_color': True,
     'skin_color': True, 
     'eye_color': True
    } 
)
for p in people.find(
    # search criteria
    {}, 
    # values to return
    {'name': True, 
     'hair_color': True,
     'skin_color': True, 
     'eye_color': True
    } 
):
    print(p)<jupyter_output><empty_output><jupyter_text>Using object IDNote that ObjectID is NOT a string. You must convert a string to ObjectID before use.From the official docs, the ObjectID consists of- a 4-byte value representing the seconds since the Unix epoch,- a 5-byte random value, and- a 3-byte counter, starting with a random value.In particular, note that sorting by ObjectIDs generated across different machines will give an approximate time ordering.<jupyter_code>result.inserted_ids[0]
people.find_one(
    result.inserted_ids[0],
    {'name': True, 'hair_color': True, 'skin_color': True, 'eye_color': True}
)<jupyter_output><empty_output><jupyter_text>Bulk queries The general `find` method returns a cursor, where each entry is a dictionary.<jupyter_code>for person in people.find(
    {'gender': 'male'}
):
    print(person['name'])<jupyter_output><empty_output><jupyter_text>You can also explicitly define the projection.<jupyter_code>for x in people.find(
    {'gender': 'male'},             
    {
        '_id': False,
        'name': True,
        'gender': True
    }
): 
    pprint(x)<jupyter_output><empty_output><jupyter_text>Using regex search<jupyter_code>for x in people.find(
    {
        'name': {'$regex': '^L'},
    },
    {
        'name': True, 
        'gender': True, 
        '_id': False
    }
):
    pprint(x)<jupyter_output><empty_output><jupyter_text>The above example uses the JavaScript regular expression syntax. You can also use Python regular expressions with `ppymongo`.<jupyter_code>import re

name_pat = re.compile(r'^l', re.IGNORECASE)
for x in people.find(
    {
        'name': name_pat,
    },
    {
        'name': True,
        'gender': True,
        '_id': False
    }
):
    pprint(x)<jupyter_output><empty_output><jupyter_text>Using relational operators<jupyter_code>for x in people.find(
    {
        'mass': {'$lt': 100},
    },
    {
        'name': True, 
        'mass': True, 
        '_id': False
    }
):
    pprint(x)
mass_range = {'$lt': 100, '$gt': 50}
for x in people.find(
    {
        'mass': mass_range,
    },
    {
        'name': True, 
        'mass': True,
        '_id': False
    }
):
    pprint(x)<jupyter_output><empty_output><jupyter_text>Nested searchNowadays, many relational databases allow you to store data as JSON columns.  However, document databases allow the convenience of nested searches.<jupyter_code>for x in people.find(
    {
        'homeworld.name': 'Tatooine',
    },
    {
        'name': True, 
        'species.name': True, 
        '_id': False
    }
):
    pprint(x)<jupyter_output><empty_output><jupyter_text>Matching multiple criteriaThis is quite subtle. By default, when matching on multiple criteria, the search is across items. Here `<NAME>` is returned because each of the 3 conditions is matched by one or more of his starships, even though none of his starships match all 3 criteria.<jupyter_code>for x in people.find(
    {
        'starships.cost_in_credits': {'$lt': 250000},
        'starships.max_atmosphering_speed': {'$gt': 500},
        'starships.passengers': {'$gt': 0}
    },
    {
        'name': True, 
        'starship.name': True, 
        'starships.max_atmosphering_speed': True,
        'starships.passengers': True,
        'starships.cost_in_credits': True,     
        '_id': False
    }
):
    pprint(x)
for x in people.find(
    {'name': '<NAME>'},
    {
        'starships.name': True,
        'starships.cost_in_credits': True,
        'starships.max_atmosphering_speed': True,
        'starships.passengers': True,
        '_id': False
    }
):
    pprint(x)<jupyter_output><empty_output><jupyter_text>Matching multiple criteria simultaneouslyTo find someone with a starship that matches all 3 conditions, we need to use the `elemMatch` operator.<jupyter_code>for x in people.find(
    {
        'starships': {
            '$elemMatch': { 
                'cost_in_credits': {'$lt': 250000},
                'max_atmosphering_speed': {'$gt': 500},
                'passengers': {'$gt': 1}
            }
        }
    },
    {
        'name': True, 
        'starships.name': True, 
        'starships.max_atmosphering_speed': True,
        'starships.passengers': True,
        'starships.cost_in_credits': True,     
        '_id': False
    }
):
    pprint(x)<jupyter_output><empty_output><jupyter_text>Aggregate Queries<jupyter_code>people.count_documents({'homeworld.name': 'Tatooine'})
people.distinct('homeworld.name')<jupyter_output><empty_output><jupyter_text>Using aggregateThe `aggregate` function runs a pipeline of commands, and uses the `$group` operator to summarize results. Within the aggregate method, you assemble a **pipeline** of operations that is executed atomically. Filter and count<jupyter_code>cmds = [
     {'$match': {'homeworld.name': 'Tatooine'}},
     {'$group': {'_id': '$homeworld.name', 
                 'count': {'$sum': 1}}},
]
for p in people.aggregate(cmds):
    pprint(p)<jupyter_output><empty_output><jupyter_text>Filter and find total mass<jupyter_code>cmds = [
     {'$match': {'homeworld.name': 'Tatooine'}},
     {'$group': {'_id': '$homeworld.name', 
                 'total_mass': {'$sum': '$mass'}}},
]
for p in people.aggregate(cmds):
    pprint(p)<jupyter_output><empty_output><jupyter_text>Total mass of all members of a planet<jupyter_code>cmds = [
     {'$group': {'_id': '$homeworld.name', 
                 'total_mass': {'$sum': '$mass'}}},
]
for p in people.aggregate(cmds):
    pprint(p)<jupyter_output><empty_output><jupyter_text>Filter, project, group by, sorting.<jupyter_code>cmds = [
     {
         '$match': {
             'mass': {
                 '$lt': 100
                     }
         },
     },
     {
         '$group': {
             '_id': '$homeworld.name',
             'total_mass': {'$sum': '$mass'},
             'avg_mass': {'$avg': '$mass'}
         },
     },
     {
        '$sort': { 
            'avg_mass': -1
        }
     }
]
for p in people.aggregate(cmds):
    pprint(p)<jupyter_output><empty_output><jupyter_text>SQL equivalent (approximate)```sqlSELECT species.name, AVG(mass) AS avg_mass, SUM(mass) AS total_massWHERE mass < 100FROM peopleJOIN speciesON people.species_id = species.species_idGROUP BY species.nameORDER BY avg_mass```  Using MapReduceWith `MapReduce` you get the full power of JavaScript, but it is more complex and often less efficient. You should use `aggregate` in preference to `map_reduce` in most cases.- In the map stage, you create a (key, value) pair- In the reduce stage, you perform a reduction (e.g. sum) of the values associated with each key  Motivating Python example<jupyter_code>from functools import reduce
eye_color = ['blue', 'blue', 'green', 'brown', 'grey', 'green', 'blue']
res = [(x, 1) for x in eye_color]
res
d = {}
for k, v in res:
    d[k] = d.get(k, 0) + v
d<jupyter_output><empty_output><jupyter_text>Map-reduce example in Mongo<jupyter_code>from bson.code import Code<jupyter_output><empty_output><jupyter_text>Count the number by eye_color<jupyter_code>mapper = Code('''
function() {
    emit(this.eye_color, 1);
}
''')

reducer = Code('''
function (key, values) {
    var total = 0;
    for (var i = 0; i < values.length; i++) {
        total += values[i];
    }
    return total;
}
''')

result = people.map_reduce(
    mapper, 
    reducer, 
    'result1'
)
for doc in result.find():
    pprint(doc)<jupyter_output><empty_output><jupyter_text>The output is also stored in the `result1` collection we specified.<jupyter_code>list(db.result1.find())<jupyter_output><empty_output><jupyter_text>Using JavaScript Array functions to simplify code.<jupyter_code>mapper = Code('''
function() {
    emit(this.eye_color, 1);
}
''')

reducer = Code('''
function (key, values) {
    return Array.sum(values);
}
''')

result = people.map_reduce(
    mapper, 
    reducer, 
    'result2'
)
for doc in result.find():
    pprint(doc)<jupyter_output><empty_output><jupyter_text>Find avergae mass by gender.<jupyter_code>mapper = Code('''
function() {
    emit(this.gender, this.mass);
}
''')

reducer = Code('''
function (key, values) {
    return Array.avg(values);
}
''')

result = people.map_reduce(
    mapper, 
    reducer, 
    'result3'
)
for doc in result.find():
    pprint(doc)<jupyter_output><empty_output><jupyter_text>Count number of members in each species<jupyter_code>mapper = Code('''
function() {
    this.species.map(function(z) {
      emit(z.name, 1);
    })
}
''')

reducer = Code('''
function (key, values) {
    return Array.sum(values);
}
''')

result = people.map_reduce(
    mapper, 
    reducer, 
    'result3'
)
for doc in result.find():
    pprint(doc)<jupyter_output><empty_output><jupyter_text>Using the `aggregate` methodSee if you can convert the above MapReduce queries to `aggregate` method calls. An example is provided.<jupyter_code>cmds = [
    {
         '$group': {
             '_id': '$eye_color',
             'count': {'$sum': 1},
         },
     },
     {
        '$sort': { 
            '_id': 1
        }
     }
]
for p in people.aggregate(cmds):
    pprint(p)<jupyter_output><empty_output><jupyter_text>Geospatial queriesYou specify queries using [GeoJSON Objects](https://docs.mongodb.com/manual/reference/geojson/)- Point- LineString- Polygon- MultiPoint- MultiLineString- MultiPolygon- GeometryCollection<jupyter_code>crime = db.crime
import json
path = 'data/crime-mapping.geojson'

with open(path) as f:
    datastore = json.load(f)
results = crime.insert_many(datastore['features'])
crime.find_one({})
crime.find_one({},
              {
                  'geometry': 1,
                  '_id': 0,
              }
              )
crime.create_index([('geometry', GEOSPHERE)])<jupyter_output><empty_output><jupyter_text>List 5 crimes near the location<jupyter_code>loc = SON([('type', 'Point'), ('coordinates', [-78.78200313, 35.760212065])])

for doc in crime.find(
    {
        'geometry' : SON([('$near', {'$geometry' : loc})])
    },
    {
        '_id': 0,
        'properties.crime_type': 1,
        'properties.date_from': 1
    }
).limit(5):
    pprint(doc)<jupyter_output><empty_output><jupyter_text>List crimes committed nearby (within 200 m)<jupyter_code>loc = SON([('type', 'Point'), ('coordinates', [-78.78200313, 35.760212065])])

for doc in crime.find(
    {
        'geometry' : SON([('$geoNear', {'$geometry' : loc, '$minDistance': 1e-6, '$maxDistance': 200})]),
    },
    {
        '_id': 0,
        'geometry.coordinates': 1,
        'properties.crime_type': 1,
        'properties.date_from': 1
    }
):
    pprint(doc)<jupyter_output><empty_output><jupyter_text>IndexesJust as with relational databases, you can add indexes to speed up search. Note that while reads become faster, writes become slower. There is always a trade-off.<jupyter_code>people.find({}).explain
people.find({'name': '<NAME>'}).explain()
people.create_index('name')
people.find({'name': '<NAME>'}).explain()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13457045591082037, 'fraction_numerical': 0.008410653494426273, 'mean_word_length': 3.4090641553855208, 'pattern_counts': {'":': 0, '<': 156, '<?xml version=': 0, '>': 154, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '37', 'dir': 'jupyter-structured-clean-dedup', 'id': '166565', 'license': 'MIT', 'n_tokens_mistral': 5139, 'n_tokens_neox': 4921, 'n_words': 1208, 'path': 'notebooks/A10_NoSQL_MongoDB.ipynb', 'repo_name': 'cliburn/bios-823-2020', 'size': '15021'}