<jupyter_start><jupyter_text>What is the aggregation framework? MongoDB provides 3 ways to perform aggregation: the aggregation pipeline, the map-reduce function, and single purpose aggregation methods.   The aggregation pipeline- Among the 3, the pipeline is the preferred method for data aggregation.- The pipeline can operate on a sharded collection, it also has an internal optimization phase.- The pipeline can use indexes to improve its performance during some of its stages.- Each document has a BSON document size limit of 16 megabytes.- Each stage in the pipeline has a memory limit of 100 megabytes of RAM.- For large datasets > 100 RAM memory, must specify `db.collection.aggregate( [{},{},...], {allowDiskUse: true} )`  ![pipeline](https://docs.mongodb.com/manual/_images/aggregation-pipeline.bakedsvg.svg) Map-Reduce- Map-Reduce = map stage + reduce stage- A map stage processes each document and emits one or more objects for each input document.- A reduce phase combines the output of the map operation.- in general, map-reduce is less efficient and more complex than the aggregation pipeline.- Map-reduce can operate on a sharded collection, it can also output to a sharded collection.  ![map-reduce](https://docs.mongodb.com/manual/_images/map-reduce.bakedsvg.svg) Single Purpose Aggregation Operationsoperations that aggregate documents from a single collection.- `db.collection.estimatedDocumentCount()`- `db.collection.count()`- `db.collection.distinct()`  ![SPAO](https://docs.mongodb.com/manual/_images/distinct.bakedsvg.svg)  MongoDB aggregation framework vs. Basic CRUD operations1. With the aggregation framework, solving complex problems are much easier. Equivalent code of CRUD will be very messy.2. The built-in aggregation operators are much faster than CRUD, and it calculates with less bandwidth.3. The aggregation framework works well with big data, while CRUD performs poorly on extremely large datasets.4. If necessary, we should prioritize the building of aggregation pipelines.  How CRUD and aggregation framework overlaps?CRUD operations represent the basic Query Framework on one side, which aims to __Create, Read, Update, Delete__ the database collections, as well as some basic shaping such as `group by`,`order by`.  On the other side, the Aggregation Framework focuses on **data derivation, analysis, basic shaping, advanced shaping and much more**. __However, the Aggregation Framework is not supposed to Create, Update or Delete the database collections, it should be our tool to analyze the collection data while leaving the original data untouched.__ To modify the collection data in any cases, we must use the basic CRUD operations.  So where do they overlap? - Both frameworks support Read and basic shaping operations, but in our mind, we should keep a line between the two.<jupyter_code># select count(*) as count from collection = 
db.collection.count()
db.collection.aggregate( [{"$group": {"_id": null, "count": {"$sum": 1}}}] )<jupyter_output><empty_output><jupyter_text>the dollar sign1. `$user`, `$user.name`  - field path expressions, use field path to access fields in the input documents  2. `$$CURRENT` - system variables = `$$` followed by an UPPERCASE word  3. `$$foo` - user variables = `$$` followed by a lowercase word  4. `$` is equivalent to `$$CURRENT.` where the `CURRENT` is a system variable that defaults to the root of the current object in the most stages, unless stated otherwise in specific stages. In our case, `$$CURRENT`simply refers to the current document.  For a list of all system variables in MongoDB, see:  https://docs.mongodb.com/manual/reference/aggregation-variables/agg-system-variables  CRUD Query Framework  intro to query & update methods<jupyter_code>'''basic query methods'''

db.collection.find(query, projection)
db.collection.findOne(query, projection)
......<jupyter_output><empty_output><jupyter_text>here's all the Query and Projection Operators to be used in those query methods  https://docs.mongodb.com/manual/reference/operator/query/<jupyter_code>'''basic update methods'''

db.collection.update()
db.collection.updateOne()
db.collection.updateMany()

db.collection.insert()
db.collection.insertOne()
db.collection.insertMany()

db.collection.findAndModify()
......<jupyter_output><empty_output><jupyter_text>here's all the Update Operators to be used in those update methods  https://docs.mongodb.com/manual/reference/operator/update/  cursor-like methods<jupyter_code>db.collection.find(query, projection) # returns a cursor
db.collection.find({}, {"_id": 0, "$name": 1}).count()
db.collection.find({}, {"_id": 0, "$name": 1}).pretty()
db.collection.find({}, {"_id": 0, "$name": 1}).skip(5).pretty()
db.collection.find({}, {"_id": 0, "$name": 1}).limit(3).pretty()
db.collection.find({}, {"_id": 0, "$name": 1}).sort({"$name": -1}).pretty()  # +1 asc, -1 desc
db.collection.find({}, {"_id": 0, "$name": 1}).sort({"$name": 1}).limit(1).pretty()<jupyter_output><empty_output><jupyter_text>find more cursor methods:  https://docs.mongodb.com/manual/reference/method/js-cursor/  also the collection methods:  https://docs.mongodb.com/manual/reference/method/js-collection/or the database methods(rarely used):  https://docs.mongodb.com/manual/reference/method/js-database/  All the cursor like methods have their corresponding aggregation stages<jupyter_code>db.collection.find().count()  # $count: {....} stage
db.collection.find().limit()  # $limit: {....} stage
db.collection.find().skip()   # $skip: {....} stage
db.collection.find().sort()   # $sort: {....} stage<jupyter_output><empty_output><jupyter_text>Aggregation Framework<jupyter_code>db.collection.aggregate([{},{},{},...], <options>)  # aggregation pipeline syntax<jupyter_output><empty_output><jupyter_text>common stages- `$addFields` = `select , * from collection;`- `$match` = `select * from collection where ;`- `$group` = `select * from collection group by ;`- `$group` + `$match` = `select * from collection group by  having ;`- `$project` = `select ,  from collection;`- `$sort` = `select * from collection order by ;`- `$limit` = `select * from collection limit ;`- `$sum` = `select sum() from collection;`- `$lookup` = `select  from  join  where ;`- `$sortByCount` = `select count() as count from collection group by  order by count;`  stage example: $match<jupyter_code>""" A $match stage may contain a $text query operator, but it must be the first stage in a pipeline
    $match should come early in an aggregation pipeline
    you cannot use $where with $match
    $match use the same query syntax as db.collection.find()
"""<jupyter_output><empty_output><jupyter_text>stage example: $project<jupyter_code>""" Once we specify one field to retain, we must specify all fields we want to retain
    if not explicitly removed, the _id field always shows up
    $project allows us to add new fields
    $project can be used as many times as required within a pipeline
    $project can be used to reassign values to existing field names and to derive entirely new fields
"""<jupyter_output><empty_output><jupyter_text>stage example: $group<jupyter_code>""" _id is where to specify what incoming documents should be grouped on
    _id can also be a newly-created field, or the evaluation of an expression
    within $group, we can use all accumulator expressions
    $group can be used multiple times within a pipeline
    
    important!: it may be necessary to sanitize incoming data, if the accumulated field is empty,
                or it's of an unexpected type, that accumulator result would be null.
                so, make sure to look at your data first, before the $group stage.
                in practice, the $group stage should be after a predicate $match stage
"""<jupyter_output><empty_output><jupyter_text>find more aggregation stages here's all the pipeline stages:  https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/ and get familiar with the aggregation operators to be used within the pipeline stages:  https://docs.mongodb.com/manual/reference/operator/aggregation/  MongoDB's flexible schema  mongoimport<jupyter_code>%run mongoimport --help  # this is too run Python scripts interactively
%%!
mongoimport --help<jupyter_output><empty_output><jupyter_text>let's use the `mongoimport` to upload a csv file into our free tier cluster  first, in MongoDB Atlas, select __Command Line Tools__ to copy the command string<jupyter_code>from IPython.display import Image
Image(filename = "/home/neo-mashiro/Pictures/Selection_001.png")
Image(filename = "/home/neo-mashiro/Pictures/Selection_002.png")
%%!
mongoimport --host mflix-shard-0/mflix-shard-00-00-tg67n.mongodb.net:27017,mflix-shard-00-01-tg67n.mongodb.net:27017,mflix-shard-00-02-tg67n.mongodb.net:27017 --ssl --username analytics --password <PASSWORD> --authenticationDatabase admin --db <DATABASE> --collection <COLLECTION> --type <FILETYPE> --file <FILENAME>
%%!  # specify the <...> fields
mongoimport --host mflix-shard-0/mflix-shard-00-00-tg67n.mongodb.net:27017,mflix-shard-00-01-tg67n.mongodb.net:27017,mflix-shard-00-02-tg67n.mongodb.net:27017 --ssl --username analytics --password <PASSWORD> --authenticationDatabase admin --db mflix --collection orders --type CSV --file retail.csv<jupyter_output><empty_output><jupyter_text>as you import, you should refresh and view your upload data in __MongoDB Compass__, check the individual document, the data type of each field, and then modify you `mongoimport` command options as necessary. If you click on the __Schema__ tag of a collection, it will give you further graphic details about each field, such as the data distribution, if there's any undefined types, and so on. __Schema__ is also a fast way to query and filter data when you click on a graph criterion. when you analyze __Schema__, MongoDB Compass will randomly choose a sample of 1,000 documents from the collection to form that statistics analysis. Remember, the best way to explore your data is through Compass, which is much faster and more inclusive than the `db.collection.find()` or `db.collection.aggregate()` operation.<jupyter_code>Image(filename = "/home/neo-mashiro/Pictures/Selection_003.png")
Image(filename = "/home/neo-mashiro/Pictures/Selection_004.png")<jupyter_output><empty_output><jupyter_text>after exploring your data in Compass, you may want to fix the import to clean up the data, parse some fields, etc., here's where the options come in<jupyter_code>'''options for mongoimport'''

--headerline  # the first row of .csv file corresponds to the field name
--columnsHaveTypes  # specify field types for each column
--drop # drop the last import and reimport, otherwise the 2nd import will append documents to the 1st import<jupyter_output><empty_output><jupyter_text>to specify the field data types, you need to modify the header row of the .csv file like this<jupyter_code>Image(filename = "/home/neo-mashiro/Pictures/Selection_005.png")<jupyter_output><empty_output><jupyter_text>there's a lot more options, check `mongoimport --help`.  the importance of Schema Design Remember the E-R Diagram in Oracle? In RDBMS, you start with the data and its relationships, but in MongoDB, schema begins with data access patterns, which is performance focused. In practice, most people care most about their query performance, so think about the query that you're going to work with most frequently, and what query do you expect to have the best performance. Ultimately, it's up to you what schema and data relationship fits you well.<jupyter_code>Image(filename = "/home/neo-mashiro/Pictures/Selection_006.png")
Image(filename = "/home/neo-mashiro/Pictures/Selection_007.png")<jupyter_output><empty_output><jupyter_text>Schema validation allows you to define a validation and only insert documents that pass the validation.  Schema changes over time, but MongoDB supports a very flexible schema migration. Even if you don't have a good schema design, even if you have designed a very bad E-R data relationship, it's okay! Since the aggregation framework allows us to reshape our data in an easy way, we can then change or migrate our schema by creating views, or using the `$out` stage to create new collections. Keep in mind, the aggregation framework does not support any write/update operations on the collection, it always returns a new copy while leaving the raw data untouched.  example of schema migration, don't execute<jupyter_code>from pymongo import MongoClient
from bson.objectid import ObjectId
from bson.decimal128 import Decimal128
import json
class JSONEncoder(json.JSONEncoder):  # a helper function
    def default(self, o):
        if isinstance(o, ObjectId) or isinstance(o, Decimal128):
            return str(o)
        return json.JSONEncoder.default(self, o)
course_cluster_uri = "mongodb://agg-student:agg-password@cluster0-shard-00-00-jxeqq.mongodb.net:27017,cluster0-shard-00-01-jxeqq.mongodb.net:27017,cluster0-shard-00-02-jxeqq.mongodb.net:27017/test?ssl=true&replicaSet=Cluster0-shard-0&authSource=admin"
course_client = MongoClient(course_cluster_uri)
collection = course_client['coursera-agg']['retail']
assemble = {
    "$group": {
        "_id": {
            "InvoiceNo": "$InvoiceNo",
            "CustomerID": "$CustomerID",
            "Country": "$Country"
        },
        "InvoiceDate": { "$max": "$InvoiceDate" },
        "Items": {
            "$push": {
                "StockCode": "$StockCode",
                "Description": "$Description",
                "Quantity": "$Quantity",
                "UnitPrice": "$UnitPrice"
            }
        }
    }
}
beautify = {
    "$project": {
        "_id": "$_id.InvoiceNo",
        "InvoiceDate": "$_id.InvoiceDate",
        "CustomerID": "$_id.CustomerID",
        "Country": "$_id.Country",
        "Items": 1
    }
}
cursor = collection.aggregate([
    assemble,
    beautify
  ],
  allowDiskUse=True)
doc = cursor.next()  # fetch a document
print(json.dumps(doc, cls=JSONEncoder, indent=4))  # convert JSON to Python dictionary
computed = {
    "$addFields" : {
        "TotalPrice": {
            "$reduce": {
                "input": "$Items",
                "initialValue": Decimal128("0.00"),
                "in": {
                    "$add": [
                        "$$value",
                        { "$multiply": [ "$$this.Quantity", "$$this.UnitPrice" ] }
                    ]
                }
            }
        }
    }
}
cursor = collection.aggregate([
    assemble,
    beautify,
    computed
  ],
  allowDiskUse=True)
doc = cursor.next()
print(json.dumps(doc, cls=JSONEncoder, indent=4))
save = {
    "$out": "orders_new"  # output results to create/overwrite a collection
}<jupyter_output><empty_output><jupyter_text>alternatively, we can create a view by `db.collection.createView(, , , )`  https://docs.mongodb.com/manual/reference/method/db.createView/index.html use `db.getCollectionInfos()` to check a view<jupyter_code>import pymongo
import json
course_cluster_uri = "mongodb://agg-student:agg-password@<EMAIL>-shard-00-00-<EMAIL>.mongodb.net:27017,cluster0-shard-00-01-jxeqq.mongodb.net:27017,cluster0-shard-00-02-jxeqq.mongodb.net:27017/test?ssl=true&replicaSet=Cluster0-shard-0&authSource=admin"
course_client = pymongo.MongoClient(course_cluster_uri)
db = course_client['aggregations']
db.getCollectionInfos()  # this command only works in Mongo Shell, but pymongo does not support it.<jupyter_output><empty_output><jupyter_text>after a view has been created, it's in the `system.views` default collection.<jupyter_code>db.system.views.find().pretty()  # this command only works in Mongo Shell, but pymongo does not support it.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10964576962283384, 'fraction_numerical': 0.013634046890927625, 'mean_word_length': 4.39601237538673, 'pattern_counts': {'":': 45, '<': 83, '<?xml version=': 0, '>': 84, 'https://': 12, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, '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': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '418409', 'license': 'CC0-1.0', 'n_tokens_mistral': 4713, 'n_tokens_neox': 4433, 'n_words': 1596, 'path': 'database/MongoDB notebooks/01_Aggregation Framework Overview.ipynb', 'repo_name': 'neo-mashiro/BU', 'size': '15703'}