How to properly search a collection and a relation at the same time?

Josh_Novak · 2024-08-15T12:32:58.337Z

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 · 2024-08-15T18:17:23.933Z

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_Lord · 2024-08-19T12:03:49.004Z

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
-Joel