I have two collections, one is Users and the other is Posts. I tried to find() in both collections and concat the result, but I faced a problem in pagination. My question is how can I achieve this in one query so I can paginate.

Example:

Users:

    {
        _id: 1
        username: 'test',
        type: 'users'
        ...
    }

Posts:

    {
      _id: 1,
      name: 'test3',
      type: 'posts',
      ...
    }

And when I search for example 'test' I should get both user data and post data with 'test' in the username/name.

Also, I tried aggregate but there is nothing common in the two tables to join. And I have a question, will these cause performance issues for many searches ?

Any help is appreciated. Thanks

CodePudding user response：

You can use $unionWith aggregation stage. It performs a union of two collections.

• $unionWith to perform union of two collections. Note that $unionWith is available starting with MongoDB version 4.2.
• $match with $regex to filter only documents where username or name field contains a specified string. Note that $options: "i" will make the search case-insensitive. If you want case-sensitive search, just remove $options: "i" from the query.

db.users.aggregate([
  {
    "$unionWith": "posts"
  },
  {
    "$match": {
      "$or": [
        {
          "username": {
            "$regex": "test",
            "$options": "i"
          }
        },
        {
          "name": {
            "$regex": "test",
            "$options": "i"
          }
        }
      ]
    }
  }
])

Working example