mongodbaggregation-framework

MongoDB aggregate group condition


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 :

  1. If the same product in various order line items for the same order has all "YES", then the product will have "YES" value of that product at the order level.
  2. If it is a mix of "YES", "NO" and "NOT_SURE", the order will have "NO"
  3. If it is a mix of "YES" and "NOT_SURE", the order will have "NOT_SURE"

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",},
        }
    }
]

Solution

    1. $unwind - Deconstruct the products array into multiple documents.

    2. $group - Group by order_id and products_name. Add the hasDefects array with the value of products.hasDefect.

    3. $set - Set the hasDefect field. Categorize the status by conditions ($switch).

    4. $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"
            }
          }
        }
      }
    ])
    

    Demo @ Mongo Playground