Sorry if the question has already been asked, but I cannot really find a solution for my particular problem.

Let's say I have multiple collections:

// collection 1 looks like this:
{
    fieldX: 'x',
    fieldY: 'y',
    date: '2022-01-15 12:00',
    condition: {
        enabled: true,
        condition: 'abc',
    }
}

// while collection 2 looks like this:
{
    fieldZ: 'z',
    date: '2022-01-15 15:25',
    condition: {
        enabled: false,
        condition: 'bce',
    }
}

As you can see the two collections have data that is very similar. Is it possible to keep the collection separate, but when querying, to return them all together?

For example, is it possible to sort the documents of both collections by date and get the first 10 in a single query?

If it's not possible, shall I just make a single collection and put both kind of documents in there? Is it possible to do when using mongoose and having a predefined schema?

CodePudding user response：

• $unionWith - To combine documents from both collections
• $sort - To sort documents by date
• $limit - To return only the first 10 documents

db.collection_1.aggregate([
  {
    "$unionWith": {
      "coll": "collection_2"
    }
  },
  {
    "$sort": {
      "date": -1
    }
  },
  {
    "$limit": 10
  }
])

Working example

CodePudding user response：

The $unionWith operation is available starting from mongodb 4.4 , it is simlilar to UNION ALL from SQL , example:

   mongos> db.col1.aggregate([   
          { $project: { date: 1, _id: 0 } }, 
          { $unionWith: { coll: "col2",
          pipeline: [ {$project: { date: 1, _id: 0 } } ] }} ,
                     {$sort:{date:-1}} ,
                     {$limit: 10}
                    ])
  { "date" : "2022-01-15 15:25" }
  { "date" : "2022-01-15 12:00" }
  mongos> 

explained:

1. In the first project stage we filter only date field from collection col1.
2. In second stage we add the date from col2 collection
3. In the pipeline we sort the result in descending order
4. In last pipeline stage result is limited to first 10

playground example