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
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"
}
}
]
}
}
])