I have this below collection and I want to use MongoDB aggregation to achieve the following output.
[
{
"order_line_item_id": 1
"order_id": 100,
"products": [
{"name": "Shoe","hasDefect": "YES",},
{"name": "Pant","hasDefect": "NO",},
{"name": "Shirt","hasDefect": "NOT_SURE",},
],
},
{
"order_line_item_id": 2
"order_id": 100,
"products": [
{"name": "Shoe","hasDefect": "YES",},
{"name": "Pant","hasDefect": "YES",},
{"name": "Shirt","hasDefect": "YES",},
],
},
{
"order_line_item_id": 3
"order_id": 101,
"products": [
{"name": "Shoe","hasDefect": "YES",},
{"name": "Pant","hasDefect": "NO",},
{"name": "Shirt","hasDefect": "NOT_SURE",},
],
},
]
I want to group these by order and roll up the "hasDefect" at the order level based on the condition :
For the above collection, for the order id:100, the expected output is something like :
[
{
"order_id": 100,
{
"products": [
{"name": "Shoe","hasDefect": "YES",},
{"name": "Pant","hasDefect": "NO",},
{"name": "Shirt","hasDefect": "NOT_SURE",},
}
}
]
$unwind
- Deconstruct the products
array into multiple documents.
$group
- Group by order_id
and products_name
. Add the hasDefects
array with the value of products.hasDefect
.
$set
- Set the hasDefect
field. Categorize the status by conditions ($switch
).
$group
- Group by _id.order_id
. Add the products
array.
db.collection.aggregate([
{
$unwind: "$products"
},
{
$group: {
_id: {
order_id: "$order_id",
productName: "$products.name"
},
hasDefects: {
$push: "$products.hasDefect"
}
}
},
{
$set: {
hasDefect: {
$switch: {
branches: [
{
case: {
$allElementsTrue: {
$map: {
input: "$hasDefects",
as: "d",
in: {
$eq: [
"$$d",
"YES"
]
}
}
}
},
then: "YES"
},
{
case: {
$in: [
"NO",
"$hasDefects"
]
},
then: "NO"
},
{
case: {
$in: [
"NOT_SURE",
"$hasDefects"
]
},
then: "NOT_SURE"
}
],
default: "<DEFAULT>"
}
}
}
},
{
$group: {
_id: "$_id.order_id",
products: {
$push: {
name: "$_id.productName",
hasDefect: "$hasDefect"
}
}
}
}
])