Get all record from collection , if matches at least one record in second collection on MongoDB

MongoDB Atlas
,
1

I have two collection. User and Course

User Collection

  [{
    "_id": "11111",
    "name": "john",
  },
  {
    "_id": "11112",
    "name": "smith",
    
  }]

Course Collection

[{
    "_id": "00011",
    "user_id": "11111",
    "location_id": "9999",
  },
  {
    "_id": "00012",
    "user_id": "11111",
    "location_id": "8888",
    
  },
  {
    "_id": "00013",
    "user_id": "11111",
    "location_id": "7777",
  },
  {
    "_id": "00014",
    "user_id": "11112",
    "location_id": "7777",
  }]

Actual Result (I am getting below result if I apply filter region_id: 7777)

[
    {
      "_id": "11111",
      "name": "john",
      "regions": [
        {
          "_id": "00013",
          "user_id": "11111",
          "location_id": "7777"
        }
      ]
    },
    {
      "_id": "11112",
      "name": "smith",
      "regions": [
        {
          "_id": "00014",
          "user_id": "11112",
          "location_id": "7777"
        }
      ]
    }
]

If I apply the filter by region_id 7777 then I want below output. If I apply the region id 7777 and 8888, I want same output. So basically, I want all user regions, if it matches at least one region_id. I am getting correct response if there is no region_id filter Expected result:

[
    {
      "_id": "11111",
      "name": "john",
      "regions": [
        {
          "_id": "00011",
          "user_id": "11111",
          "location_id": "9999"
        },
        {
          "_id": "00012",
          "user_id": "11111",
          "location_id": "8888"
        },
        {
          "_id": "00013",
          "user_id": "11111",
          "location_id": "7777"
        }
      ]
    },
    {
      "_id": "11112",
      "name": "smith",
      "regions": [
        {
          "_id": "00014",
          "user_id": "11112",
          "location_id": "7777"
        }
      ]
    }
]

Below is my aggregate query

db.user.aggregate([
  {
    "$match": {}
  },
  {
    "$lookup": {
      "from": "region",
      "localField": "_id",
      "foreignField": "user_id",
      "as": "regions"
    }
  },
  {
    "$addFields": {
      "regions": {
        "$filter": {
          input: "$regions",
          as: "region",
          cond: {
            $in: [
              "$region.location_id",
              [
                "7777"
              ]
            ]
          }
        }
      }
    }
  }
])
2

Can you clarify the above? You so not have any field named region_id so it is not clear how you would filter by region_id:7777. The values 7777 and 8888 seems to be associated with a field named location_id. So I feel you sample documents are redacted from real documents.

Despite some information that is not clear what I think could work is

  • Replace the $addFields at the top level by an inner pipeline in the $lookup that $match on location_id.

The updated $lookup would look like:

3

@Digvijaya_Singh, it has been a week since I provided some insight about your issue.

I would appreciate a follow up.

Have you tried what I proposed?
If you did tried it and it failed share the error or wrong result it provided.
If you did try it and it work, please mark my post as the solution.
If you found another solution, please share what it is and mark this post as the solution.