Mongo 聚合排除一个表中的记录，具体取决于它们是否在另一个表中找到

Question

公寓表

{
"_id": "AAA",
"name": "Apartment AAA"
},
{
"_id": "BBB",
"name": "Apartment BBB"
},
{
"_id": "CCC",
"name": "Apartment CCC"
},
{
"_id": "DDD",
"name": "Apartment DDD"
},
{
"_id": "EEE",
"name": "Apartment EEE"
}

预订表

{
"_id": 1,
"apartmentID": "AAA",
"checkin": 1490000000,
"checkout": 1499000000
}
{
"_id": 2,
"apartmentID": "BBB",
"checkin": 1500000000,
"checkout": 1590000000
}
{
"_id": 3,
"apartmentID": "CCC",
"checkin": 1490000000,
"checkout": 1499000000
}
{
"_id": 4,
"apartmentID": "DDD",
"checkin": 1500000000,
"checkout": 1590000000
}

我需要找到所有"apartment"的"name"，这些"在"1510000000"和"1520000000"之间没有预订

在这种情况下，结果应该是：

{
"name": "Apartment AAA"
},
{
"name": "Apartment CCC"
},
{
"name": "Apartment EEE"
}

请注意，EEE公寓不接受任何预订。

通常我会将所有属性放入一个数组中，然后将所有预订放入一个数组中，然后在 javascript 中运行一些循环以查找可用的公寓。

问题：有没有办法在一个mongodb聚合管道中做到这一点，并只返回可用公寓的"名称"？

我的旧查找：

db.apartment.aggregate([
{
$match: {}
},
{ 
$project: { 
"name": 1
}
}

db.booking.aggregate([
{
$match: {
checkin: {$lte: 1520000000},
checkout: {$gte: 1510000000}
}
},
{ 
$project: { 
"apartmentID": 1
}
}

Answer 1

您可以使用$not + $elemMatch来查找是否有任何公寓预订被占用。

$elemMatch查找是否有符合查询条件的已占用公寓，然后在没有占用预订时$not返回公寓文件。

类似的东西

db.apartment.aggregate({
$lookup: {
from: "booking",
localField: "_id",
foreignField: "apartmentID",
as: "bookings"
}
}, {
$match: {
"bookings": {
$not: {
$elemMatch: {
checkin: {
$lte: 1520000000
},
checkout: {
$gte: 1510000000
}
}
}
}
}
}, {
$project: {
_id: 0,
name: 1
}
})

Answer 2

从版本 3.2 开始，您可以使用$lookup运算符将两个集合外部联接：

db.apartment.aggregate([
{
$lookup: {
from: "booking",
localField: "_id",
foreignField: "apartmentID",
as: "booking"
}
},
{
$unwind: { path: "$booking", preserveNullAndEmptyArrays: true }
},
{
$match: {
$or: [
{ "booking": {$exists: false }},
{ "booking.checkin": {$gte: 1520000000} },
{ "booking.checkout": {$lte: 1510000000} }
]
}
},
{
$group: { _id: "$name" }
},
{
$project: { _id: 0, name: "$_id" }
}
])

输出：

{
"name" : "Apartment AAA"
},
{
"name" : "Apartment CCC"
},
{
"name" : "Apartment EEE"
}

---

详：

第一阶段创建公寓与其预订的外部连接。此阶段产生的结果如下所示：

{
"_id": "AAA",
"name": "Apartment AAA",
"booking": [ 
{
"_id": 1,
"apartmentID": "AAA",
"checkin": 1490000000,
"checkout": 1499000000
},
{
"_id": 5,
"apartmentID": "AAA",
"checkin": 1500000000,
"checkout": 1590000000
},
]
}
...
{
"_id": "EEE",
"name": "Apartment EEE"
}

请注意，如果公寓AAA有多个预订，则所有预订文件都将添加到booking公寓阵列中。另请注意，没有预订的公寓将不提供booking阵列。接下来，我们展开联接的预订数组以摆脱数组并使用单个预订(如果有)生成扁平的公寓对象：

{
"_id": "AAA",
"name": "Apartment AAA",
"booking": {
"_id": 1,
"apartmentID": "AAA",
"checkin": 1490000000,
"checkout": 1499000000
}
},
{
"_id": "AAA",
"name": "Apartment AAA",
"booking": {
"_id": 5,
"apartmentID": "AAA",
"checkin": 1500000000,
"checkout": 1590000000
}
}
...
{
"_id": "EEE",
"name": "Apartment EEE"
}

接下来，我们按公寓名称进行过滤和分组过滤结果(因为一间公寓可以有多个不属于给定范围的预订)。最后阶段 - 投影。