时间戳

首页 > 解决方案 > MongoDB条件搜索与父文档和数组中的嵌套文档

mongodb - MongoDB条件搜索与父文档和数组中的嵌套文档

问题描述

假设我有一个这样的 mongodb“模式”：

offers: Schema = {
      name: String,
      validityStart: Date,
      validityEnd: Date,
      customized: [
         {
            validityStart: Date,
            validityEnd: Date,
            user: String
         }
     ]
}

通过文字：这里我们有一组报价，在validityStart 和validityEnd 指定的日期之间有效。每个优惠都有一个子数组，它定义用户可以“定制”这个优惠，即有更长的使用时间。

我可以查询所有可用的优惠

Offer.find({ $and: [{ validityStart: { $lte: today } }, { validityEnd: { $gte: today } }] })

但如果提供了“用户”，我需要创建一个查询，显示所有优惠并通过用户字段过滤显示“自定义”优惠。

展示我想要实现的目标的一个例子是：想象一下在 12 月有一些报价

[
    {
      name: "Soccer Game 2020",
      validityStart: "2019-12-1",
      validityEnd: "2019-12-31"
    },
    {
      name: "Golf Equipment",
      validityStart: "2019-12-1",
      validityEnd: "2019-12-31"
    }
]

但是我让用户 FOO 成为足球比赛 1 个月的早起鸟，我将拥有：

[
    {
      name: "Soccer Game 2020",
      validityStart: "2019-12-1",
      validityEnd: "2019-12-31",
      customized:[
        {
           validityStart: "2019-11-1",
           validityEnd: "2019-11-30",
           user: "FOO"
        }
      ]
    },
    ---
]

如何为所有用户返回“父”日期，但对于自定义数组指定的某些用户，他们各自的日期？像：

[
    {
      name: "Soccer Game 2020",
      validityStart: "2019-11-1",
      validityEnd: "2019-11-30"
    },
    {
      name: "Golf Equipment",
      validityStart: "2019-12-1",
      validityEnd: "2019-12-31"
    }
]

我不知道如何创建这样的查询。

我可以更改架构，我处于开发的早期阶段，所以这不是问题。

标签： mongodbmongooseaggregation-framework

解决方案

事实证明，这比我希望的解决方案更冗长，但可以完成工作。此外，如果日期当前是字符串，则仅建议将日期作为date对象存储在文档中。

询问：

db.collection.aggregate([
  {
    $addFields: {
      userCustomOffer: {
        $filter: {
          input: "$customized",
          as: "elem",
          cond: {
            $eq: ["$$elem.user", "FOO"]
          }
        }
      }
    }
  },
  {
    $project: {
      name: 1,
      validityStart: {
        $cond: {
          if: {
            $or: [
              {
                $eq: ["$userCustomOffer", []]
              },
              {
                $eq: ["$userCustomOffer", null]
              }
            ]
          },
          then: "$validityStart",
          else: {
            $arrayElemAt: ["$userCustomOffer.validityStart", 0]
          }
        }
      },
      validityEnd: {
        $cond: {
          if: {
            $or: [
              {
                $eq: ["$userCustomOffer", []]
              },
              {
                $eq: ["$userCustomOffer", null]
              }
            ]
          },
          then: "$validityEnd",
          else: {
            $arrayElemAt: ["$userCustomOffer.validityEnd", 0]
          }
        }
      }
    }
  }
]);

作为用例的示例文档：

[
  {
    name: "Soccer Game 2020",
    validityStart: "2019-12-1",
    validityEnd: "2019-12-31",
    customized: [
      {
        validityStart: "2019-11-1",
        validityEnd: "2019-11-30",
        user: "FOO"
      },
      {
        validityStart: "2019-10-1",
        validityEnd: "2019-10-31",
        user: "BAZ"
      }
    ]
  },
  {
    name: "Golf Equipment",
    validityStart: "2019-12-1",
    validityEnd: "2019-12-31",
    customized: [
      {
        validityStart: "2019-11-1",
        validityEnd: "2019-11-30",
        user: "BAR"
      }
    ]
  },
  {
    name: "Hockey Kit",
    validityStart: "2019-01-1",
    validityEnd: "2019-02-28"
  }
]

查询结果：

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "name": "Soccer Game 2020",
    "validityEnd": "2019-11-30",
    "validityStart": "2019-11-1"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "name": "Golf Equipment",
    "validityEnd": "2019-12-31",
    "validityStart": "2019-12-1"
  },
  {
    "_id": ObjectId("5a934e000102030405000002"),
    "name": "Hockey Kit",
    "validityEnd": "2019-02-28",
    "validityStart": "2019-01-1"
  }
]

推荐阅读