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.
If I've understood correctly you can try something like this:
$group by productName and store orderids in an array$match by values in the array: $and condition where exists 1 and not 2.$unwind and $project with the axuliar field root saved in the $group stagedb.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