SQL'UNION ALL'在MongoDB中的实现 [英] SQL 'UNION ALL' like implementation in MongoDB
问题描述
有两个集合:
销售
{
"_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"
}
交易
{
"_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
}
]
}
我需要检索按日期排序的两个日期(即saleDate,transactionDate)之间的特定方(即customerName,partyName)的销售和交易作为标题";如下:
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.
从SQL切换到MongoDB.在SQL中,以下查询工作正常:
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
在MongoDB社区中有一个功能请求,它是未解决的".
There's a feature request filed in the MongoDB community and it's 'unresolved'.
我想知道mongoShell或MongoDB驱动程序(mongoose/JS)中对此有什么办法.使用当前稳定的MongoDB,nodejs,express和mongoose版本.谢谢!
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!
推荐答案
您可以在下面的汇总中尝试
You can try below aggregation
db.sales.aggregate([
{ "$limit": 1 },
{ "$facet": {
"collection1": [
{ "$limit": 1 },
{ "$lookup": {
"from": "sales",
"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 }}
])
这篇关于SQL'UNION ALL'在MongoDB中的实现的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!