Can MongoDB perform the following query?
select id, title from posts
union all
select id, name from programs
sort by title asc;
I am looking to combine and sort documents from two different collections as one. Any assistance would be appreciated.
Additional Details
Specifically, I am conducting a text search across 2 collections and want to merge the results:
This is how my Express route handler is set up:
// Post and Program are Mongoose models
function (req, res) {
const criteria = req.params.criteria
Promise.all([
Post
.find({$text: {$search: criteria}}, { title: 1, score: { $meta: 'textScore' }})
.sort({ score: { $meta: 'textScore' } })
.exec(),
Program
.find({$text: {$search: criteria}}, { title: 1, score: { $meta: 'textScore' }})
.sort({ score: { $meta: 'textScore' } })
.exec()
])
.then(results => reply([...results[0], ...results[1]]))
.catch(err => handle(err))
}
While I can manipulate the results array using lodash, the challenge lies in displaying the results paginated, requiring fetching the necessary data per page on each request.
If I use lodash, it means fetching all data from the DB every time and then selecting the right page with lodash. It would be more efficient to fetch only what is required.
Hence, I hope for a solution like this:
Post
.find({$text: {$search: criteria}}, { title: 1, score: { $meta: 'textScore' }})
.unionAll(
Program
.find({$text: {$search: criteria}}, { title: 1, score: { $meta: 'textScore' }})
)
.sort({ score: { $meta: 'textScore' } })
.limit(10)
.exec()
Though it may seem like wishful thinking, is there a way to achieve these results directly from MongoDB, or do I need to rely on external libraries like lodash?