Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams "_id" : ObjectId("5ba0bfb8d1acdc0de716e839"), "invoiceNumber" : 1, "saleDate" : ISODate("2018-09-01T00:00:00.000Z"), "totalTaxAmount" : 613, "subTotalAmount" : 2000, "totalAmount" : 2613, "balance" : 2613, "financialYear" : "2018-2019", "modeOfPayment" : "Digital Transfer", "customerName": "Acme Inc"

Transactions

"_id" : ObjectId("5bbb4e131fb8af0dc645212d"), "transactionNumber" : 1 "transactionDate" : ISODate("2018-09-03T00:00:00.000Z"), "transactionType" : "Income", "partyName" : "Acme Inc", "transactionMode" : "Digital Transfer", "amount" : 2613, "paidItems" : [ "orderId" : "5b90a7d62bb5a21be4ff97e3", "invoiceNumber" : "1", "orderType" : "sale", "totalAmount" : 2613, "balance" : 613, "payingAmount" : 2000

I need to retrieve sales and transactions as 'heading' for the specific party (i.e. customerName, partyName) between two dates (i.e. saleDate, transactionDate) ordered by date; as follows:

"date": ISODate("2018-09-01T00:00:00.000Z"), "heading": "Sale", "particulars": "Invoice # 1", "amount": 2613 "date": ISODate("2018-09-03T00:00:00.000Z"), "heading": "Payment by Digital Transfer", "particulars": "Transaction # 1", "amount": 2000

I researched and tried with aggregation , $lookup but it's not returning what's desired.

Switching from SQL to MongoDB. In SQL following query works fine:

select sale_date as dated, 'Sale' as heading, 'Invoice # ' + 
convert(varchar(12),invoice_number) as particulars, 
convert(varchar(12), total) as amount, 
from sales where sale_date between @from_date AND @to_date AND 
customer_name=@customer_name
UNION ALL
select transaction_date as dated, 'Payment by ' + transaction_mode as 
heading, 'Transaction # ' + convert(varchar(12), transaction_id) as 
particulars, convert(varchar(12), amount) as amount from transactions 
where transaction_date between @from_date AND @to_date AND 
party_name=@customer_name
order by dated DESC
There's a feature request filed in the MongoDB community and it's 'unresolved'. 
I would like to know is there any way for this within mongoShell or MongoDB driver (mongoose / JS). Using current stable versions of MongoDB, nodejs, express, and mongoose. Thanks!
                Sure. This is not a full proof as I was just giving it a try.         db.sales.aggregate([   { $match: { "saleDate":{$gte: ISODate("2018-09-01"),$lte: ISODate("2018-09-10")}, "customerName":'Acme Inc' }},   { $lookup: {          from: "transactions",          localField: "customerName",          foreignField: "partyName",           as: "fromTransactions"       }    },    {       $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$fromTransactions", 0 ] }, "$$ROOT" ] } }    },    { $project: { saleDate: 1, invoiceNumber: 1, amount:1, fromTransactions: 1 } } ])
– Man In
                Oct 9, 2018 at 8:48
                This is retrieving sale entries each documented with an array of transactions with matching customerName. I know this is not at all correct but i was trying it with aggregate but seems that it's not what I want as I need to get sales AND transactions ordered by date. Or may be I'm making a mistake.
– Man In
                Oct 9, 2018 at 8:53
                @AnthonyWinzlet Keeping in mind, 16 MB BSON limit of $lookup the solution you proposed is good for interim use. Same as of the question you referred, what about sorting by date (it's not been mentioned in the answer); are we supposed to sort resulting concatenated 'data' collection? I specified it in the query but no help. Any suggestions.
– Man In
                Oct 10, 2018 at 5:13
        "pipeline": [
          { "$match": {
            "date": { "$gte": ISODate("2018-09-01"), "$lte": ISODate("2018-09-10") },
            "customer.name": customerName
          { "$project": {
            "_id":0, "dated": "$saleDate", "heading": "Sale", "particulars": "$invoiceNumber",
            "amount": "$totalAmount", "modeOfPayment": null
        "as": "collection1"
    "collection2": [
      { "$limit": 1 },
      { "$lookup": {
        "from": "transactions",
        "pipeline": [
          { "$match": {
            "transactionDate": { "$gte": ISODate("2018-09-01"), "$lte": ISODate("2018-09-10") },
            "userId": userId, "partyName": customerName
          { "$project": {
            "_id":0, "dated": "$transactionDate", "heading": "Payment","particulars": "$transactionNumber",
            "amount": "$amount", "paymentMode": "$transactionMode"
        "as": "collection2"
  { "$project": {
    "data": {
      "$concatArrays": [
        { "$arrayElemAt": ["$collection1.collection1", 0] },
        { "$arrayElemAt": ["$collection2.collection2", 0] },
  { "$unwind": "$data" },
  { "$replaceRoot": { "newRoot": "$data" } },
  { "$sort": { "dated": -1 }}
                Until you have $match condition you don't need to worry. allowDiskUse: true will only help to handle large data set.
– Ashh
                Oct 13, 2018 at 11:34
I dont think so that you can perform a union on 2 different collections in mongodb.
However you can get data from both collections using q.all and afterwards make a union from them using your own function or may be third party module like lodash. 
The only way you can perform union in mongodb is as mentioned here.
https://docs.mongodb.com/manual/reference/operator/aggregation/setUnion/
                @SagarChaudhary I see. I had a thought about it, but I was wondering if there is any way. If not possible in MongoDB shell then retrieving two different collections and formatting them as per requirement is the only option I'm left with.
– Man In
                Oct 9, 2018 at 8:57
Disclaimer: The technique presented below is not exactly advisable. ;) This is true in particular when dealing with big collections. However, it can be used to achieve the identical effect as a SQL UNION ALL from MongoDB v3.6 onwards.
Given a collection first and a collection second:
db.first.aggregate([{
    $group: { // create an array to hold all documents of the first collection
        "_id": null,
        "first": {
            $push: "$$ROOT"
    $lookup: { // perform some kind of ridiculous lookup which will return all documents from the second collection in an array
        from: "second",
        let: { /* we do not need any variables */ },
        pipeline: [ { $match: { /* this filter will match every document */ } } ],
        as: "second"
    $project: {
        "all": { $concatArrays: [ "$first", "$second" ] } // merge the two collections
    $unwind: "$all" // flatten the resulting array
    $replaceRoot: { "newRoot": "$all" } // move content of "all" field all the way up 
}], {
    allowDiskUse: true // make sure we do not run into memory issues
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.