I have a single mongo collection called "CourseCollection" and it contains both parent and child doc. Any document with the key "Parent" is a child doc and a parent can have multiple child doc.

{
    "_id" : "abracadavra",
    "Name" : "abracadavra",
    "Description" : "",
    "Type" : "Spell",
    "Parent" : {
        "_id" : "Magic",
        "Type" : "Course",
        "Name" : "Magic"
    }
},
{
    "_id" : "Magic",
    "Name" : "Magic",
    "Type" : "Course",
    "Access" : [ 
        {
            "_id" : "2sssdw5oe",
            "Name" : "Abc"
        }, 
        {
            "_id" : "4fddfye42",
            "Name" : "Xyz"
        }
    ]
}

What I'm trying to do is, based on the Access of Parent doc, I'm trying to get all the child doc.

Existing and working solution:

The solution that I have currently is to perform 2 queries.

Query 1. Get all the courses that the user has access to.

db.getCollection("CourseCollection").find({"Type": "Course", "Access._id": {"$in": ["2sssdw5oe"]}})

Query 2. Since I'm using Python, I do a list comprehension to get only the IDs of the course and then perform another query with this list

db.getCollection("CourseCollection").find({"Type": "Spell", "Parent._id": {"$in": course_list_id}})

Is there a way to get the child data after filtering out the parent in a single query. I also tried aggregation but only the results of the previous stage are passed to the next stage.

CodePudding user response：

I guess you're trying to do something like this:

db.CourseCollection.aggregate([
  {
    "$match": {
      "Type": "Spell"
    }
  },
  {
    "$lookup": {
      "from": "CourseCollection",
      "localField": "Parent._id",
      "foreignField": "_id",
      "as": "Parents"
    }
  },
  {
    "$match": {
      "Parents": {
        "$elemMatch": {
          "Type": "Course",
          "Access._id": {
            "$in": [
              "2sssdw5oe"
            ]
          }
        }
      }
    }
  }
])

You can achieve the same result doing this too:

db.CourseCollection.aggregate([
  {
    "$match": {
      "Type": "Spell"
    }
  },
  {
    "$lookup": {
      "from": "CourseCollection",
      "localField": "Parent._id",
      "foreignField": "_id",
      "as": "Parents",
      "pipeline": [
        {
          "$match": {
            "Type": "Course",
            "Access._id": {
              "$in": [
                "2sssdw5oe"
              ]
            }
          }
        }
      ]
    }
  },
  {
    "$match": {
      "Parents.0": {
        "$exists": true
      }
    }
  }
])