How can I use mongodb $lookup and $unionWith to get documnets from two collections-CodePudding

1st collection

stocks = [
{"userId" : 1, "groupId": 1, "stockId": 1},
{"userId": 2, "groupId": 1, "stockId": 2},
{"userId": 3, "groupId": 4, "stockId": 3}
]

2nd collection:

items = [
{"userid": 1, "groupId": 1, "itemId": 1},
{"userid": 1, "groupId": 3, "itemId": 2},
{"userid": 1, "groupId": 4, "itemId": 3}
]

I have a collection user, from which i get the userid, here i have filtered to get userid as 1, I have tried the below lookup, i am getting all data for userid, but when I add condition to exclude group, its not working. can someone help or suggest where i am doing wrong?

{
  from: "stocks",
  localField: "user.id",
  foreignField: "userId",
  let: {group_id: "$groupId", user_id: "$userId" },
  pipeline: [{ "$unionWith": { coll: "items", pipeline: [{$match: {$userid: "$$user_id", "$groupId": { $nin: ["$$group_id"]}}}]}}],
  as: "stock_items"
}

I need list of data where userId and groupId should not be same, i need all the data from stocks and items excluding item[0], since both have same user id and group id.

CodePudding user response：

I'm not entirely sure that I understand what you are asking for. I'm also confused by the sample aggregation that has been provided because fields like "user.id" doesn't exist in the sample documents. But at the end you specifically mention:

i need all the data from stocks and items excluding item[0], since both have same user id and group id

Based on that, this answer assumes that you are looking to find all documents in both collections where the value of the groupId field is different than the value of the userId field. If that is correct, then the following query should work:

db.stocks.aggregate([
  {
    $match: {
      $expr: {
        $ne: [
          "$groupId",
          "$userId"
        ]
      }
    }
  },
  {
    "$unionWith": {
      "coll": "items",
      "pipeline": [
        {
          $match: {
            $expr: {
              $ne: [
                "$groupId",
                "$userId"
              ]
            }
          }
        }
      ]
    }
  }
])

Playground demonstration here.

The way this operates is by using the $ne aggregation operator to compare the two fields in the document. We need to use the $expr operator to do this comparison as shown here in the documentation.