Using an array as a query argument in MongoDB-CodePudding

I have a MongoDB database structured as such:

People Database, Person Document 1:

{
      _id: ObjectId("61f9d7dd837c1fb343a21550"),
      alias: [
        "Lewis",
        "Colt"
      ]
}

Report Database, Report Document 1:

{
      _id: ObjectId("61fc1f59bf1edbf56d24acad"),
      people: [
        "Lewis",
        "Jake"
      ],
      Report: "They talked about Python and C."
    }

Report Database, Report Document 2:

{
      _id: ObjectId("61fc29e73cd13b44ffc4074b"),
      people: [
        "Colt",
        "Justin"
      ],
      Report: "They tweaked the cogs."
    }

Is it possible to use the alias's array in the person document to pull up the bottom two documents? For example, I could insert Lewis as the query when searching reports, it would find the Person Document, look at the different names provided in alias, and find the Report Documents that contain any string in the alias array in the people array and display the relevant reports.

Fetch Reports with Lewis, Recognizes that Lewis is also Colt, Displays reports with either Lewis or Colt in the People array.

More context, I am using Python 3.10 with Pymongo and Discord.py, using a bot to display the documents.

Is this possible in MongoDB?

CodePudding user response：

You can do a sub-pipeline in a $lookup. Compute the set intersection between alias array in people collection and people array in report collection. $match those $setIntersection's size > 0.

db.people.aggregate([
  {
    "$match": {
      // put your query here
      alias: "Lewis"
    }
  },
  {
    "$lookup": {
      "from": "report",
      "let": {
        alias: "$alias"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $gt: [
                {
                  $size: {
                    "$setIntersection": [
                      "$$alias",
                      "$people"
                    ]
                  }
                },
                0
              ]
            }
          }
        }
      ],
      "as": "matchedReports"
    }
  },
  {
    "$unwind": "$matchedReports"
  },
  {
    "$replaceRoot": {
      "newRoot": "$matchedReports"
    }
  }
])

Here is the Mongo playground for your reference.