CODEWITHSUNDEEP
javascriptsqlmongodb
acmoune
acmoune

Reputation: 3423

MongoDB: Set operations on documents sets, how to?

Is it possible to do this in MongoDB ?

select id, title from posts
union all
select id, name from programs
sort by title asc;

I would like to sort documents from two distinct collections, and treat the result as one collection. Please help.

Added

In fact, I am doing a text search, but I want to search on 2 collections and merge the results:

My express route handler looks like this:

// Post and Program are a 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))
}

I know I can manipulate the results array like I want with lodash, but the problem is that I must display the results page by page, so on each request I should fetch the number of document displayed per page.

If I go for lodash, then I will always fetch all data from the DB, then choose the right page with lodash. It will be more efficient to fetch just what is needed.

So I am hoping there is a way to do something 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()

I know I am dreaming, but it can make sense. So is there a way to do get that results from MongoDB, or do I have to manage it with lodash or something like.

Upvotes: 1

Views: 57

Answers (1)

Ashh
Ashh

Reputation: 46481

You can try with $facet and $lookup aggregation 

db.collection.aggregate([
  { "$limit": 1 },
  { "$facet": {
    "c1": [
      { "$lookup": {
        "from": Post.collection.name,
        "pipeline": [
          { "$match": { "$text": { "$search": criteria }} },
          { "$project": { "title": 1 }}
        ],
        "as": "collection1"
      }}
    ],
    "c2": [
      { "$lookup": {
        "from": Program.collection.name,
        "pipeline": [
          { "$match": { "$text": { "$search": criteria }} },
          { "$project": { "name": 1 }}
        ],
        "as": "collection2"
      }}
    ]
  }},
  { "$project": {
    "data": {
      "$concatArrays": [ "$c1", "$c2" ]
    }
  }},
  { "$unwind": "$data" },
  { "$replaceRoot": { "newRoot": "$data" } }
])

Upvotes: 1

Related Questions