Aug 2024
3 / 3
Aug 2024
Aug 2024
Josh_Novak
Aug 2024

I’m trying to figure out how to properly search a collection and a relation at the same time?

Lets say I have Users setup like this:

{
  _id: ObjectId("1234"),
  firstName: "John",
  lastName: "Smith"
}

And Posts setup like this:

{
  _id: ObjectId("8877"),
 author: ObjectId("1234") 
 title: "My Post",
}

How could I do a search on posts that could grab any posts that match my query on post.title or user.firstName or user.lastName?
Josh_Novak
Aug 2024

I’ve tried an aggregate with $lookup and have gotten it close. This narrows down the nested relation, but i’m not sure how to also check the current collection.

[
  {
    
          $lookup: {
            from: 'users',
            localField: 'user',
            foreignField: '_id',
            as: 'user',
            pipeline: [
              {
                $search: {
                  index: 'users',
                  compound: {
                    should: [
                      {
                        search: {
                          query: "Jim",
                          path: 'user.firstName',
                        },
                      },
                    ],
                  },
                },
              },
            ],
          },
  },
  { $unwind: '$user' },
]
Joel_LordMongoDB Employee
Aug 2024

You could use a $match as the second stage of your pipeline:

[
  {
    $lookup:
      {
        from: "Users",
        localField: "author",
        foreignField: "_id",
        as: "author"
      }
  },
  {
    $match:
      {
        $or: [
          {
            title: "John"
          },
          {
            "author.firstName": "John"
          },
          {
            "author.lastName": "John"
          }
        ]
      }
  }
]

I would also suggest looking into the Extended Reference Pattern, which is very common for this use case. With this data modeling schema pattern, you would change your Posts documents to look like:

{
  _id: ObjectId("8877"),
 author: {
    ObjectId("1234"),
    firstName: "John",
    lastName: "Smith"
 },
 title: "My Post",
}

This way, you can query your data without the need for a $lookup. Note that you would only put the fields that are needed for querying as part of the author property, not a full duplication of the Author document. Do take a look at the article for more details.

I hope this helps :slight_smile:
-Joel


Want to read more? Browse other topics in Working with Data or view latest topics.