Question 417260 in Mongodb Question posted in
The official documentation can be found here.

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

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    Instead of filtering regions, just match on region.location_id having any of the regions you’re filtering on. So if any array items have that, the whole document (with the full regions array) is returned.

    db.user.aggregate([
  {
    "$lookup": {
      "from": "region",
      "localField": "_id",
      "foreignField": "user_id",
      "as": "regions"
    }
  },
  {
    $match: {
      // your location filter here
      "regions.location_id": { $in: ["7777", "8888"] }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search