MongoDB聚合中如何避免$unwind和$group

Question

我收藏了"马酒店"&;在MongoDB。我需要创建如下查询:

• 10月23-24日有效
• 至少可容纳2人

我已经成功地创建了一个使用$unwind和$group(以及更多)工作的聚合管道，但是查询似乎不必要地臃肿和复杂。有更好的方式来创造它吗?

集合中的文档(酒店)示例:

{
"_id": {
"$oid": "616c18d09542c9e6882a7dce"
},
"userId": {
"$oid": "61247e2f8cc1782a4f6762fe"
},
"active": true,
"status": "approved",
"title": "Test hotel 10",
"description": "Lorem ipsum dolor sit amet",
"rooms": [
{
"type": "house",
"isShared": false,
"isHorseRoom": false,
"spots": 4,
"dayPrice": 150,
"includedSpots": 2,
"extraSpotDayPrice": 30,
"reservations": [
{
"reservationId": "someReservationId",
"fromDate": {
"$date": "2021-10-23T00:00:00Z"
},
"toDate": {
"$date": "2021-10-24T00:00:00Z"
},
"spots": 3
},
{
"fromDate": {
"$date": "2021-10-16T00:00:00Z"
},
"toDate": {
"$date": "2021-10-17T00:00:00Z"
},
"spots": 1,
"reservationId": "someReservationId"
}
]
},
{
"type": "hayloft",
"isShared": true,
"isHorseRoom": false,
"spots": 10,
"dayPrice": 40,
"includedSpots": 1,
"extraSpotDayPrice": 40
},
{
"type": "grass",
"isShared": false,
"isHorseRoom": true,
"spots": 4,
"dayPrice": 50,
"reservations": [
{
"reservationId": "someReservationId",
"fromDate": {
"$date": "2021-10-30T00:00:00Z"
},
"toDate": {
"$date": "2021-10-31T00:00:00Z"
},
"spots": 2
},
{
"reservationId": "someReservationId",
"fromDate": {
"$date": "2021-11-06T00:00:00Z"
},
"toDate": {
"$date": "2021-11-07T00:00:00Z"
},
"spots": 2
}
]
},
{
"type": "grass",
"isShared": false,
"isHorseRoom": true,
"spots": 2,
"dayPrice": 100
},
{
"type": "box",
"isShared": false,
"isHorseRoom": true,
"spots": 1,
"dayPrice": 80
},
{
"type": "box",
"isShared": false,
"isHorseRoom": true,
"spots": 1,
"dayPrice": 80
}
],
"weekDiscountPercentage": 0.2,
"priceChanges": [
{
"fromDate": {
"$date": "2021-07-01T00:00:00Z"
},
"toDate": {
"$date": "2021-07-31T00:00:00Z"
},
"percentage": 1.2
}
]
}

我当前的工作查询

[
// Stage 1: Only keep active and approved hotels
{
$match: {
status: "approved",
active: true,
},
},
// Stage 2: Filter the 'rooms' to only keep rooms which is either: 
// a) shared, b) has no reservations or c) is not shared and has a reservation in that time frame.
{
$addFields: {
rooms: {
$filter: {
input: "$rooms",
as: "room",
cond: {
$or: [
{
$eq: ["$$room.isShared", true],
},
{
$eq: ["$$room.reservations", []],
},
{
$not: ["$$room.reservations"],
},
{
$and: [
{
$eq: ["$$room.isShared", false],
},
{
$eq: [
{
$filter: {
input: "$$room.reservations",
as: "reservation",
cond: {
$and: [
{
$gte: [
"$$reservation.fromDate",
ISODate("2021-10-23"),
],
},
{
$lt: [
"$$reservation.fromDate",
ISODate("2021-10-24"),
],
},
],
},
},
},
[],
],
},
],
},
],
},
},
},
},
},
// Stage 3: Unwind rooms
{
$unwind: {
path: "$rooms",
},
},
// Stage 4: We know all these rooms are either shared and/or available
// This calculates how many spots are reserved within the date range
{
$project: {
spots: "$rooms.spots",
_id: "$_id",
type: "$rooms.type",
title: "$title",
dayPrice: "$rooms.dayPrice",
isShared: "$rooms.isShared",
reserved: {
$sum: {
$map: {
input: "$rooms.reservations",
as: "item",
in: {
$cond: [
{
$and: [
{
$gte: [
"$$item.fromDate",
ISODate("2021-10-23"),
],
},
{
$lte: [
"$$item.fromDate",
ISODate("2021-10-24"),
],
},
],
},
"$$item.spots",
0,
],
},
},
},
},
},
},
// Stage 5: Add a new field containing the number of spots left available in the time frame for each room
{
$addFields: {
free: {
$subtract: ["$spots", "$reserved"],
},
},
},
// Stage 6: Group the unwinded documents together again by the hotel id
// and calculate the total available spots
{
$group: {
_id: "$_id",
title: { $first: "$title" },
rooms: {
$push: {
name: "$_id.actName",
spots: "$spots",
dayPrice: "$dayPrice",
reserved: "$reserved",
type: "$type",
free: "$free",
},
},
freecount: { $sum: "$free" },
},
},
// Stage 7: Only keep the hotels which have e.g. minimum 2 free spots left (depending on user input)
{
$match: {
freecount: { $gte: 2 },
},
},
];

Answer 1

好的，我创建了一个解决方案，使用addFields级，$sum和两个$map级。

这是我新的addFields阶段，它取代了上面的第2、3、4、5和6阶段。

{
reservedSpots: {
"$sum": {
"$map": {
"input": "$rooms",
"as": "room",
"in": {
"$sum": {
"$map": {
"input": "$$room.reservations",
"as": "reservation",
"in": {
"$cond": [
// Check if the reservation dates matches
{ 
"$and": [
{ "$gte": ["$$reservation.fromDate", ISODate('2021-10-22')] }, 
{ "$lte": ["$$reservation.toDate", ISODate('2021-10-25')] }
]
}, 
// If yes, then
{
$cond: [
// Check if the room is shared
{ $eq: ["$$room.isShared", false] },
// If yes, then add all spots of the room
"$$room.spots",
// If no, only add the spots of the reservation
"$$reservation.spots"
]
},
// If no, don't add to $sum
0
]
}
}
}

}
}
}
}
}