I have this set of documents

{_id: ObjectId(""),orderid:1,productName:"Iphone 13"}
{_id: ObjectId(""),orderid:1,productName:"Xiaomi 11"}
{_id: ObjectId(""),orderid:1,productName:"Pocophone F1"}
{_id: ObjectId(""),orderid:1,productName:"Samsung S22"}
{_id: ObjectId(""),orderid:2,productName:"Iphone 13"}
{_id: ObjectId(""),orderid:2,productName:"Xiaomi 11"}

I am trying to get the product names that are in order 1 but not in order 2, my mongodb query was:

{orderid:{$eq:orderid-1,$ne:orderid}}

but it does not work, then I was trying with an aggregate op like:

{
    $match: {orderid:{$gte:orderid-1}} //this is because allways a will try to get the products name in max orderid - 1 to get that resutl with the last 2 orders
    $group:{
       _id: {productname: '$productName'}
       orders:{$push:'$orderid'}
       total:{$sum:1}
    }
    //Then here i'm trying to get just de maxorderid-1 docs that just have total equal 1 but i cant achive the result.
}

Thanks you so much for your support and answers.

CodePudding user response：

If I've understood correctly you can try something like this:

• First $group by productName and store orderids in an array
• The $match by values in the array: $and condition where exists 1 and not 2.
• And then rebuild the objects using $unwind and $project with the axuliar field root saved in the $group stage

db.collection.aggregate([
  {
    "$group": {
      "_id": "$productName",
      "orderids": {
        "$push": "$orderid"
      },
      "root": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$match": {
      "$and": [
        {
          "orderids": 1
        },
        {
          "orderids": {
            "$ne": 2
          }
        }
      ]
    }
  },
  {
    "$unwind": "$root"
  },
  {
    "$project": {
      "_id": "$root._id",
      "orderid": "$root.orderid",
      "productName": "$root.productName"
    }
  }
])

Example here

Also, if you only want the productName, that value is stored in the id of the group, soy uo don't need the last two stages. Like this example