MongoDB条件搜索，包含数组中的父文档和嵌套文档

Question

假设我有一个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 } }] })

但如果提供了">user"，我需要创建一个查询，显示所有优惠，并通过用户字段进行筛选来显示"自定义"优惠。

一个展示我想要实现的目标的例子是：想象一下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"
}
]

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

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

Answer 1

这比我想要的解决方案要详细一点，但完成了任务。此外，只建议将日期存储为文档中的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"
}
]