mongodb - 带有 $cond 的 mongo 聚合在嵌套查找中不能与 $eq 一起使用

Question

我在$cond语句中引用嵌套数组项时遇到问题。

db.getCollection('bookings').aggregate([
  {
    $lookup: {
      from: "listings",
      localField: "listingId",
      foreignField: "_id",
      as: "listing"
    }
  },
  {
    $match: {
      $and: [
        {
          locationId: ObjectId("5c0f0c882fcf07fb08890c27")
        },
        {
          $or: [
            {
              $and: [
                {
                  state: "booked"
                },
                {
                  startDate: {
                    $lte: new Date()
                  }
                },
                {
                  startDate: {
                    $gte: ISODate("2019-12-18T07:00:00.000Z")
                  }
                }
              ]
            },
            {
              $and: [
                {
                    listing: {
                        $elemMatch: { 
                            inspectionStatus: "none" 
                         }
                    }
                },
                {
                  endDate: {
                    $lte: new Date()
                  }
                },
                {
                  endDate: {
                    $gte: ISODate("2019-12-18T07:00:00.000Z")
                  }
                },
                {
                  state: {
                    $in: [
                      "active",
                      "returned"
                    ]
                  }
                }
              ]
            },
            {
              $and: [
                {
                  state: {
                    $ne: "cancelled"
                  }
                },
                {
                  $or: [
                    {
                      $and: [
                        {
                          startDate: {
                            $gte: ISODate("2019-12-20T07:00:00.993Z")
                          }
                        },
                        {
                          startDate: {
                            $lte: ISODate("2019-12-21T06:59:59.999Z")
                          }
                        }
                      ]
                    },
                    {
                      $and: [
                        {
                          endDate: {
                            $gte: ISODate("2019-12-20T07:00:00.993Z")
                          }
                        },
                        {
                          endDate: {
                            $lte: ISODate("2019-12-21T06:59:59.999Z")
                          }
                        }
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    }
  },
  {
    $addFields: {
      isLate: {
        $cond: [
          {
            $or: [
              {
                $and: [
                  {
                    $eq: [
                        "$listing.0.inspectionStatus",
                      "none"
                    ]
                  },
                  {
                    $lte: [
                      "$endDate",
                      new Date()
                    ]
                  },
                  {
                    $gte: [
                      "$endDate",
                      ISODate("2019-12-18T07:00:00.000Z")
                    ]
                  },
                  {
                    $in: [
                      "$state",
                      [
                        "active",
                        "returned"
                      ]
                    ]
                  },

                ]
              },
              {
                $and: [
                  {
                    $eq: [
                      "$state",
                      "booked"
                    ]
                  },
                  {
                    $lte: [
                      "$startDate",
                      new Date()
                    ]
                  },
                  {
                    $gte: [
                      "$startDate",
                      ISODate("2019-12-18T07:00:00.000Z")
                    ]
                  }
                ]
              }
            ]
          },
          true,
          false
        ]
      }
    }
  }
])

在上面，$cond语句中的以下几行根本不起作用：

$eq: [
   "$listing.0.inspectionStatus",
   "none"
]

我的问题是 - 我该如何进行上述工作？请注意，listing查找后字段中始终只有一个数组项（其中永远不会超过一个数组项）。我尝试了不同的变化，例如$listing.$0.$inspectionStatus- 但似乎没有任何效果。我可以沿着研究的轨迹前进group-filter但是当我只想访问listing数组中的第一个也是唯一一个项目时，我觉得这有点过头了。

标签： mongodbmongoosemongodb-queryaggregation-framework

Answer 1

请在 $cond 关键字中使用$in关键字而不是$eq关键字

db.demo1.aggregate([
      {
        $lookup: {
          from: "demo2",
          localField: "listingId",
          foreignField: "_id",
          as: "listing"
        }
      },
      {
        $match: {
          $and: [
            {
              locationId: ObjectId("5c0f0c882fcf07fb08890c27")
            },
            {
              $or: [
                {
                  $and: [
                    {
                      state: "booked"
                    },
                    {
                      startDate: {
                        $lte: new Date()
                      }
                    },
                    {
                      startDate: {
                        $gte: ISODate("2019-12-18T07:00:00.000Z")
                      }
                    }
                  ]
                },
                {
                  $and: [
                    {
                        listing: {
                            $elemMatch: { 
                                inspectionStatus: "none" 
                             }
                        }
                    },
                    {
                      endDate: {
                        $lte: new Date()
                      }
                    },
                    {
                      endDate: {
                        $gte: ISODate("2019-12-18T07:00:00.000Z")
                      }
                    },
                    {
                      state: {
                        $in: [
                          "active",
                          "returned"
                        ]
                      }
                    }
                  ]
                },
                {
                  $and: [
                    {
                      state: {
                        $ne: "cancelled"
                      }
                    },
                    {
                      $or: [
                        {
                          $and: [
                            {
                              startDate: {
                                $gte: ISODate("2019-12-20T07:00:00.993Z")
                              }
                            },
                            {
                              startDate: {
                                $lte: ISODate("2019-12-21T06:59:59.999Z")
                              }
                            }
                          ]
                        },
                        {
                          $and: [
                            {
                              endDate: {
                                $gte: ISODate("2019-12-20T07:00:00.993Z")
                              }
                            },
                            {
                              endDate: {
                                $lte: ISODate("2019-12-21T06:59:59.999Z")
                              }
                            }
                          ]
                        }
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        }
      },

      {
        $addFields: {
          isLate: {
            $cond: [
              {
                $or: [
                  {
                    $and: [
                      {
                        $in: [
                            "none",
                            "$listing.inspectionStatus",

                        ]
                      },
                      {
                        $lte: [
                          "$endDate",
                          new Date()
                        ]
                      },
                      {
                        $gte: [
                          "$endDate",
                          ISODate("2019-12-18T07:00:00.000Z")
                        ]
                      },
                      {
                        $in: [
                          "$state",
                          [
                            "active",
                            "returned"
                          ]
                        ]
                      },

                    ]
                  },
                  {
                    $and: [
                      {
                        $eq: [
                          "$state",
                          "booked"
                        ]
                      },
                      {
                        $lte: [
                          "$startDate",
                          new Date()
                        ]
                      },
                      {
                        $gte: [
                          "$startDate",
                          ISODate("2019-12-18T07:00:00.000Z")
                        ]
                      }
                    ]
                  }
                ]
              },
              true,
              false
            ]
          }
        }
      }
    ])