问题描述
我有两个收集预订和发票，我准备了以下条件的汇总和查找查询
I have two collection bookings and invoices and I have prepared aggregate and lookup query for below condition
预订收集条件
条件 1:状态不等于已送达
条件 2:产品不能为 null 或为空
条件 3:ProductID 应该存在于 products 数组中，并且不能为空
条件 4:IsDeliveryFailed 不应为是"
预订收集数据
{
"_id" : ObjectId("609a382b589346973c84c6fe"),
"Name" : "abc",
"UserId":1
"Status" : "Pending",
"Invoices" : [
ObjectId("709a382b5c6fe89346973c84")
],
"BookingData" : {
"Date" : ISODate("2021-04-30T04:00:00.000Z"),
"info" : [],
"BookingDataMethod" : "avf",
"Message" : null,
"products" : [
{
"_id" : ObjectId("60a4e92775e5de3570578820"),
"ProductName" : "Test1",
"ProductID" : ObjectId("60a4e92475e5de357057880a"),
"IsDeliveryFailed" : "Yes"
},
{
"_id" : ObjectId("60a4e92775e5de357057881f"),
"ProductName" : "Test2",
"ProductID" : ObjectId("60a4e92475e5de357057880d")
}
],
}
}
发票收集条件
条件 1:InvoiceData 不应为 null 或为空
条件 2:InvoiceID 应存在于 InvoiceData 数组中，且不应为空
条件 3:IsPaymentFailed 不应为是"
发票收集数据
{
"_id" : ObjectId("709a382b5c6fe89346973c84"),
"invoiceNumber":1
"InvoiceData" :[
{
"_id" : ObjectId("60a4e92775e5de3570578820"),
"ProductName" : "Test1",
"InvoiceID":1,
"IsPaymentFailed" : "Yes"
},
{
"_id" : ObjectId("60a4e92775e5de357057881f"),
"InvoiceID":2,
"ProductName" : "Test2",
}
]
}
查询
db.bookings.aggregate([
{
"$match": {
"Status": {
$ne: "Delivered"
}
}
},
{
"$lookup": {
"from": "invoices",
"localField": "Invoices",
"foreignField": "_id",
"as": "invoiceInfo"
}
},
{
"$match": {
"$or": [
{
"BookingData.products": {
"$exists": true
}
},
{
"invoiceInfo.InvoiceData": {
"$exists": true
}
}
]
}
},
{
$set: {
"BookingData.products": {
"$filter": {
"input": "$BookingData.products",
"cond": {
$and: [
{ $ne: [ "$$this.ProductID", undefined ] },
{ $ne: [ "$$this._id", null ] },
{ $ne: [ "$$this.IsDeliveryFailed", "Yes" ] }
]
}
}
}
}
},
{
$set: {
"invoiceInfo.InvoiceData": {
"$filter": {
"input": "$invoiceInfo.InvoiceData",
"cond": {
$and: [
{ $ne: [ "$$this.InvoiceID", undefined ] },
{ $ne: [ "$$this._id", null ] },
{ $ne: [ "$$this.IsPaymentFailed", "Yes" ] }
]
}
}
}
}
},
{
$match: {
$expr: {
$or: [
{
$ne: [
"$BookingData.products",
[],
]
},
{
$ne: [
"$invoiceInfo.InvoiceData",
[],
]
}
]
}
}
}
])
这不能按预期工作，例如如果
this is not working as expected for example if
查询应该返回上面的文档
The query should return the above document
如果 ProductID 存在且产品存在且所有产品都没有 IsDeliveryFailed:是"
if ProductID exist and products exist and all products do not have IsDeliveryFailed: "Yes"
如果 ProductID 存在且产品存在且任何产品都没有 IsDeliveryFailed:是"
if ProductID exist and products exist and anyone products do not have IsDeliveryFailed: "Yes"
如果 InvoiceID 存在且 InvoiceData 存在且所有 InvoiceData 都没有 IsPaymentFailed:是"
if InvoiceID exist and InvoiceData exist and all InvoiceData do not have IsPaymentFailed: "Yes"
如果 InvoiceID 存在且 InvoiceData 存在且任何 InvoiceData 没有 IsPaymentFailed:是"
if InvoiceID exist and InvoiceData exist and anyone InvoiceData does not has IsPaymentFailed: "Yes"
另一套
如果 ProductID 存在且产品存在且所有产品都为 IsDeliveryFailed:是"；旗帜.但我们必须检查发票收集如果 InvoiceID 存在并且 InvoiceData 存在并且任何 InvoiceData 没有 IsPaymentFailed:是"那么我们必须返回这个文件
if ProductID exists and products exist and all products are IsDeliveryFailed: "Yes" flag. but we have to check invoices collectionif InvoiceID exist and InvoiceData exist and anyone InvoiceData does not have IsPaymentFailed: "Yes" then we have to return this document
如果 InvoiceID 存在且 InvoiceData 存在且所有 InvoiceData 均为 IsPaymentFailed:是".但我们必须检查预订集合如果 ProductID 存在且产品存在且任何产品都没有 IsDeliveryFailed:是"那么我们必须返回文档
if InvoiceID exist and InvoiceData exist and all InvoiceData are IsPaymentFailed: "Yes".but we have to check bookings collectionif ProductID exist and products exist and anyone products does not have IsDeliveryFailed:"Yes" then we have to returndocument
推荐答案
我已经在问题中解释了您之前的问题.由于invoiceInfo 是一个数组，而invoiceData 也是invoiceInfo 中的一个数组，我们使用map 和filter.然后我们需要排除 invoiceData 的空数组.(这也可以在上一步完成，就像 filter-map->filter 一样，但它可能很长，这就是我在下一阶段使用它的原因)
I have explained your previous problem in Question. Since the invoiceInfo is an array and invoiceData also an array inside invoiceInfo, we use map and filter. Then we need to exclude empty array of invoiceData. (This can be done in previous step also like filter-map->filter, but it could be lengthy, thats why I used it in next stage )
这是代码
db.bookings.aggregate([
{
"$match": {
"PaymentStatus": { $ne: "Delivered" }
}
},
{
$set: {
"BookingData.products": {
"$filter": {
"input": "$BookingData.products",
"cond": {
$and: [
{ $ne: [ "$$this.ProductID", undefined ] },
{ $ne: [ "$$this._id", null ] },
{ $ne: [ "$$this.IsDeliveryFailed", "Yes" ] }
]
}
}
}
}
},
{
"$lookup": {
"from": "invoices",
"localField": "Invoices",
"foreignField": "_id",
"as": "invoiceInfo"
}
},
{
$set: {
invoiceInfo: {
$map: {
input: "$invoiceInfo",
as: "info",
in: {
InvoiceData: {
$filter: {
input: "$$info.InvoiceData",
as: "data",
"cond": {
$and: [
{ $ne: [ "$$data.InvoiceID", undefined ] },
{ $ne: [ "$$data.InvoiceID", null ] },
{ $ne: [ "$$data.IsPaymentFailed", "Yes" ] }
]
}
}
}
}
}
}
}
},
{
$set: {
invoiceInfo: {
$filter: {
input: "$invoiceInfo",
cond: { $ne: [ "$$this.InvoiceData", [] ] }
}
}
}
},
{
$match: {
$expr: {
$or: [
{ $ne: [ "$BookingData.products", [] ] },
{ $ne: [ "$invoiceInfo", [] ] }
]
}
}
}
])
工作 Mongo 游乐场
希望对你有帮助.这是您需要根据您的要求播放/解决方法的时间.有时你需要在demo中的当前位置之前或之后进行查找
Hope this will help you. This is the time you need to play/ workaround based on your requirement. Sometimes you need to do lookup before or after the current location in demo
这篇关于我如何为多个集合做 Mongodb 聚合过滤器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！