1

I don't have much experience with mongodb, so the following query is making it difficult for me.

This is the document

[
{
    "_id": "31-07-2019",
    "date": "31-07-2019",
    "grocerie1": [
        {
            "name": "Flour",
            "price": 3.68,
            "count": 1
        },
        {
            "name": "Rice",
            "price": 3,
            "count": 1
        },
        {
            "name": "Rice",
            "price": 3,
            "count": 1
        },
        {
            "name": "Flour",
            "price": 3.68,
            "count": 1
        }
    ],
    "grocerie2": [
        {
            "name": "Flour",
            "price": 3.68,
            "count": 1
        }
    ],
    "grocerie1Total": 13.36,
    "grocerie2Total": 3.68,
    "total": 17.04
},
{
    "_id": "09-08-2019",
    "date": "09-08-2019",
    "grocerie1": [
        {
            "name": "Rice",
            "price": 3,
            "count": 1
        },
        {
            "name": "Rice",
            "price": 3,
            "count": 1
        },
        {
            "name": "Milk",
            "price": 5,
            "count": 1
        }
    ],
    "grocerie2": [
        {
            "name": "Milk",
            "price": 5,
            "count": 1
        },
        {
            "name": "Cheese",
            "price": 2,
            "count": 1
        }
    ],
    "grocerie1Total": 11,
    "grocerie2Total": 7,
    "total": 18
},
{
    "_id": "22-08-2019",
    "date": "22-08-2019",
    "grocerie1": [
        {
            "name": "Rice",
            "price": 3,
            "count": 1
        },
        {
            "name": "Cheese",
            "price": 2,
            "count": 1
        },
        {
            "name": "Cheese",
            "price": 2,
            "count": 1
        },
        {
            "name": "Rice",
            "price": 3,
            "count": 1
        }
    ],
    "grocerie2": [
        {
            "name": "Rice",
            "price": 3,
            "count": 1
        },
        {
            "name": "Rice",
            "price": 3,
            "count": 1
        },
        {
            "name": "Rice",
            "price": 3,
            "count": 1
        }
    ],
    "grocerie1Total": 10,
    "grocerie2Total": 9,
    "total": 19
}
]

The document is sorted by date, and contains two grocery stores, each with different products sold. Each product has a name, price and a "count" that I placed to, in the future, obtain the number of times the product was sold through the sum of this field.

Now I want to achieve something like this:

[
{
    "_id": "31-07-2019",
    "date": "31-07-2019",
    "grocerie1": [
        {
            "name": "Flour",
            "total": 7.56,
            "count": 2
        },
        {
            "name": "Rice",
            "total": 6,
            "count": 2
        }
    ],
    "grocerie2": [
        {
            "name": "Flour",
            "total": 3.68,
            "count": 1
        }
    ],
    "grocerie1Total": 13.36,
    "grocerie2Total": 3.68,
    "total": 17.04
},
{
    "_id": "09-08-2019",
    "date": "09-08-2019",
    "grocerie1": [
        {
            "name": "Rice",
            "total": 6,
            "count": 2
        },
        {
            "name": "Milk",
            "total": 5,
            "count": 1
        }
    ],
    "grocerie2": [
        {
            "name": "Milk",
            "total": 5,
            "count": 1
        },
        {
            "name": "Cheese",
            "total": 2,
            "count": 1
        }
    ],
    "grocerie1Total": 11,
    "grocerie2Total": 7,
    "total": 18
},
{
    "_id": "22-08-2019",
    "date": "22-08-2019",
    "grocerie1": [
        {
            "name": "Rice",
            "total": 6,
            "count": 2
        },
        {
            "name": "Cheese",
            "total": 4,
            "count": 2
        }
    ],
    "grocerie2": [
        {
            "name": "Rice",
            "total": 9,
            "count": 3
        }
    ],
    "grocerie1Total": 10,
    "grocerie2Total": 9,
    "total": 19
}
]

I tried something like this, for example, for "grocerie1", however, I got disastrous results:

{
    $unwind:
    {
       path: "$grocerie1",
       preserveNullAndEmptyArrays: true
    }
},
{
    "$group": {

    "_id": "$grocerie1.name",
    "eatHereInfo": { 
       "$push": { 
          "name": "$grocerie1.name", 
          "total": { "$sum": "$grocerie1.price" }, 
          "count": { "$sum": "$grocerie1.count" } } 
    },
    "grocerie2": { "$first": "$grocerie2" },
    "date": { "$first": "$date" },
    "grocerie1Total": { "$first": "$grocerie1Total" },
    "grocerie2Total": { "$first": "$grocerie2Total" },
   }
},

Is there any way to achieve it with the aggregation framework? or with javascript? Any help and suggestion are appreciated :)

1 Answer 1

2

Note: I assume your objects stored in grocerie collection.

Mongo way (Difficult and rigid)

db.getCollection('grocerie').aggregate([
    // ---------------- We start with grocerie1 ------------------
    //1. Split grocerie1 array into atomic object
    {"$unwind":{ "path": "$grocerie1", "preserveNullAndEmptyArrays": true }},
    //2. Group by date + grocerie1 name. If group only by grocerie1.name we may group from other days 
    // For same grocerie names, we accumulate their name, price, total "grocerie1": { "$push": "$grocerie1" },
    {"$group": {
        "_id": { "_id": "$_id", "name": "$grocerie1.name" },
        "grocerie1": { "$push": "$grocerie1" },
        "grocerie2": { "$first": "$grocerie2" },
        "date": { "$first": "$date" },
        "grocerie1Total": { "$first": "$grocerie1Total" },
        "grocerie2Total": { "$first": "$grocerie2Total" }
        }
    },
    //3. Now we have unique date + grocerie1 names + all same items inside grocerie1 array. Split again into atomic value
    {"$unwind":{ "path": "$grocerie1", "preserveNullAndEmptyArrays": true }},

    //4. We group again date + grocerie1 names, but now we sum price and count
    {"$group": {
       "_id": { "_id": "$date", "name": "$_id.name" },
        "total": { "$sum": "$grocerie1.price" },
        "count": { "$sum": "$grocerie1.count" },
        "grocerie2": { "$first": "$grocerie2" },
        "date": { "$first": "$date" },
        "grocerie1Total": { "$first": "$grocerie1Total" },
        "grocerie2Total": { "$first": "$grocerie2Total" }
      }
    },
    //5. We group for date and push inside grocerie1 calculated price, total
    {"$group":{
        "_id": "$_id._id",
        "grocerie1": { "$push": {
            "name" : "$_id.name",
            "total" : "$total",
            "count" : "$count"
            } },
        "grocerie2": { "$first": "$grocerie2" },
        "date": { "$first": "$date" },
        "grocerie1Total": { "$first": "$grocerie1Total" },
        "grocerie2Total": { "$first": "$grocerie2Total" }
        }
     },
     // ---------------- We finished with grocerie1 ---------------
     // ---------------- We start with grocerie2 ------------------
     //1. Split grocerie2 array into atomic object
    {"$unwind":{ "path": "$grocerie2", "preserveNullAndEmptyArrays": true }},
    //2. Group by date + grocerie2 name. If group only by grocerie2.name we may group from other days 
    // For same grocerie names, we accumulate their name, price, total "grocerie2": { "$push": "$grocerie2" },
    {"$group": {
        "_id": { "_id": "$_id", "name": "$grocerie2.name" },
        "grocerie1": { "$first": "$grocerie1" },
        "grocerie2": { "$push": "$grocerie2" },
        "date": { "$first": "$date" },
        "grocerie1Total": { "$first": "$grocerie1Total" },
        "grocerie2Total": { "$first": "$grocerie2Total" }
        }
    },
    //3. Now we have unique date + grocerie2 names + all same items inside grocerie2 array. Split again into atomic value
    {"$unwind":{ "path": "$grocerie2", "preserveNullAndEmptyArrays": true }},

    //4. We group again date + grocerie2 names, but now we sum price and count
    {"$group": {
       "_id": { "_id": "$date", "name": "$_id.name" },
        "total": { "$sum": "$grocerie2.price" },
        "count": { "$sum": "$grocerie2.count" },
        "grocerie1": { "$first": "$grocerie1" },
        "date": { "$first": "$date" },
        "grocerie1Total": { "$first": "$grocerie1Total" },
        "grocerie2Total": { "$first": "$grocerie2Total" }
      }
    },
    //5. We group for date and push inside grocerie2 calculated price, total
    {"$group":{
        "_id": "$_id._id",
        "grocerie1": { "$first": "$grocerie1" },
        "grocerie2": { "$push": {
            "name" : "$_id.name",
            "total" : "$total",
            "count" : "$count"
            } },
        "date": { "$first": "$date" },
        "grocerie1Total": { "$first": "$grocerie1Total" },
        "grocerie2Total": { "$first": "$grocerie2Total" },
        // Sum total values
        "total" : {"$sum":{"$add":["$grocerie1Total", "$grocerie2Total"]}}
        }
     }
     // ---------------- We finished with grocerie2 ---------------
])

Javascript way (Easy and flexible)

/**
 * Group groceries with same name and sum fields
 */
function groupGroceries(){
    //aux function to group groceries with same name
    function _(grocerie){
        for(var i=grocerie.length-1; i > -1; i--){
            for(var j=0; j<i; j++){
                // If grocerie.name already exists, we sum values and remove from array
                if(grocerie[j].name == grocerie[i].name){
                    grocerie[j].price += grocerie[i].price;
                    grocerie[j].count += grocerie[i].count;
                    grocerie.splice(i, 1);
                    break;
                }
            }
        }

        //Change price into total
        for(var i=0; i<grocerie.length; i++){
            //Robo 3T bug: (""+grocerie[i].price).indexOf(".") > -1 ? grocerie[i].price : NumberInt(grocerie[i].price);
            grocerie[i].total = grocerie[i].price;
            delete grocerie[i].price;
        }
    }

    var result = [];

    //Iterate over grocerie collection
    db.getCollection('grocerie').find({}).forEach(function(doc){
        //Uncomment line below if _id disappears
        //doc["_id"];
        _(doc.grocerie1);
        _(doc.grocerie2);
        doc.total = doc.grocerie1Total + doc.grocerie2Total;
        result.push(doc);
    })

    for(var i=0; i<result.length; i++){
        print("/* " + (i+1) + " */")
        print(result[i])
        print("")
    }
}

groupGroceries();

==Result==

/* 1 */
{
    "_id" : "31-07-2019",
    "grocerie1" : [ 
        {
            "name" : "Flour",
            "total" : 7.36,
            "count" : 2
        }, 
        {
            "name" : "Rice",
            "total" : 6,
            "count" : 2
        }
    ],
    "grocerie2" : [ 
        {
            "name" : "Flour",
            "total" : 3.68,
            "count" : 1
        }
    ],
    "date" : "31-07-2019",
    "grocerie1Total" : 13.36,
    "grocerie2Total" : 3.68,
    "total" : 17.04
}

/* 2 */
{
    "_id" : "09-08-2019",
    "grocerie1" : [ 
        {
            "name" : "Rice",
            "total" : 6,
            "count" : 2
        }, 
        {
            "name" : "Milk",
            "total" : 5,
            "count" : 1
        }
    ],
    "grocerie2" : [ 
        {
            "name" : "Milk",
            "total" : 5,
            "count" : 1
        }, 
        {
            "name" : "Cheese",
            "total" : 2,
            "count" : 1
        }
    ],
    "date" : "09-08-2019",
    "grocerie1Total" : 11,
    "grocerie2Total" : 7,
    "total" : 36
}

/* 3 */
{
    "_id" : "22-08-2019",
    "grocerie1" : [ 
        {
            "name" : "Cheese",
            "total" : 4,
            "count" : 2
        }, 
        {
            "name" : "Rice",
            "total" : 6,
            "count" : 2
        }
    ],
    "grocerie2" : [ 
        {
            "name" : "Rice",
            "total" : 9,
            "count" : 3
        }
    ],
    "date" : "22-08-2019",
    "grocerie1Total" : 10,
    "grocerie2Total" : 9,
    "total" : 19
}
Sign up to request clarification or add additional context in comments.

2 Comments

It worked great. I had also found a way to get the expected result using javascript, however, I am not sure that it is the best way in terms of speed. Thank you very much for the help! :)
You are welcome. Well, it depends on your data. If you have about 1M documents, aggregation goes more faster then JS, because native implementation is faster then JS interpretation. For few documents, both has the same speed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.