title: SQL 'UNION ALL' like implementation in MongoDB
body: <p>There are two collections:</p>
  
  <p>Sales</p>
  
  <pre><code>{
    "_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"
  }
  </code></pre>
  
  <p>Transactions</p>
  
  <pre><code>{
    "_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
        }
     ]
  }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>[
    {
      "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
    }
  ]
  </code></pre>
  
  <p>I researched and tried with <a href="https://docs.mongodb.com/manual/reference/command/aggregate/index.html" rel="nofollow noreferrer">aggregation</a>, <a href="https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/index.html" rel="nofollow noreferrer">$lookup</a> but it's not returning what's desired.</p>
  
  <p>Switching from SQL to MongoDB. In SQL following query works fine:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>There's a <a href="https://jira.mongodb.org/browse/SERVER-23201" rel="nofollow noreferrer">feature request</a> filed in the MongoDB community and it's 'unresolved'. </p>
  
  <p>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!</p>