mongodbmongodb-queryaggregation-framework

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


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",
  }]

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"
              ]
            ]
          }
        }
      }
    }
  }
])

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"
        }
      ]
    }
]

Solution

  • Don't exactly understand what your filtering requirement, especially in the cases that no match on the input filtering list (e.g. ["6666"], so no region is matched). But here is what I guess you want to do with $filter: apply a $anyElementTrue after $lookup in the $filter.

    db.user.aggregate([
      {
        "$lookup": {
          "from": "region",
          "localField": "_id",
          "foreignField": "user_id",
          "as": "regions"
        }
      },
      {
        "$set": {
          "regions": {
            "$filter": {
              "input": "$regions",
              "as": "r",
              "cond": {
                "$anyElementTrue": {
                  "$map": {
                    "input": "$regions",
                    "as": "rr",
                    "in": {
                      "$in": [
                        "$$rr.location_id",
                        [
                          // your input filter here
                          "7777",
                          "8888"
                        ]
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      }
    ])
    

    Mongo Playground