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”