Matching an item name from Collection A, whose ID is local to Collection B in MongoDB with NodeJS-CodePudding

I have a two collections,

Collection journalists: {
_id: ObjectId("6125f93f5fb88535beecd8ee")
journalist_FirstName: Shyam,
journalist_LastName: Kapoor,
.........
_id: ObjectId("6125f26b5fb88535beecd8c7"),
journalist_FirstName: Kabita
journalist_LastName: Mohan
}

Collection Stories: {
_id: ObjectId("5ec3ca7fa248314329a5a287"),
journalistId:ObjectId("6125f26b5fb88535beecd8c7"), //local id to journalists collection
storyTitle: "Joining of two tables",
briefDescription: "MongoDB joining of tables",
.....
}

Now, if someone just type "Kabita" in the search field, then my query should display the story with title, as "Kabita" is the journalist responsible to create this story.

"Joining of two tables"
MongoDB joining of tables
 publish date: 21st Nov 2021

My search query needs to consider any words that matches with the name of any journalist from journalists collection, along with any words from either story title or description from stories collection.

Thanks in advance and awaiting for fitting replies.

CodePudding user response：

A simple $lookup in an aggregation will do your job. You can test it here.

db.journalists.aggregate([
  {
    "$lookup": {
      "from": "Stories",
      "localField": "_id",
      "foreignField": "journalistId",
      "as": "stories_docs"
    }
  }
])

Then you have to separe each stories doc to perform the $match with $unwind

  {
    "$unwind": "$stories_docs"
  }

After this step you could add a $matchstage to find the information that you need, if you have to find inside all of those field i will use a $regexbut the query wont be very efficient...

  {
    $match: {
      $or: [
        {
          "journalist_FirstName": {
            "$regex": "tables",
            "$options": "i"
          }
        },
        {
          "stories_docs.briefDescription": {
            "$regex": "tables",
            "$options": "i"
          }
        },
        {
          "stories_docs.storyTitle": {
            "$regex": "tables",
            "$options": "i"
          }
        },
        
      ]
    }
  },
  {
    "$match": {
      "stories_docs.keywordName": {
        "$regex": "Heritage",
        "$options": "i"
      }
    }
  }