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 :)