基于嵌套数组条件的mongoDB updateMany
基于嵌套数组条件的mongoDB updateMany
提问于 2020-02-10 11:59:27
我在users集合中有以下结构：
[
{ "name": "Ivan",
"payments": [
{"date": new Date("2019-01-01"), "details": [{"payment_system": "A", "spent": 95},
{"payment_system": "B", "spent": 123}]},
{"date": new Date("2019-01-03"), "details": [{"payment_system": "A", "spent": 12},
{"payment_system": "B", "spent": 11}]}]},
{ "name": "Mark",
"payments": [
{"date": new Date("2019-01-01"), "details": [{"payment_system": "D", "spent": 456},
{"payment_system": "B", "spent": 123}]},
{"date": new Date("2019-01-02"), "details": [{"payment_system": "A", "spent": 98},
{"payment_system": "C", "spent": 4}]}]}
]有没有办法在特定的支付系统中，在特定的日期范围内，向那些花费超过100美元的用户添加一个字段？我尝试过updateMany，但是不知道如何过滤基于payment_system字段的“细节”数组元素。
对于payment_system IN ("A", "C"), date >= "2019-01-02", spent_total >= 100，更新应该返回
[
{ "name": "Ivan", ...},
{ "name": "Mark", "filter_passed": true, ... }
]回答 1
Stack Overflow用户
回答已采纳
发布于 2020-02-10 12:35:06
这个这个：
db.collection.aggregate([
{
$set: {
payments: {
$filter: {
input: "$payments",
cond: { $gte: ["$$this.date", new Date("2019-01-02")] }
}
}
}
},
{
$set: {
spent_total: {
$reduce: {
input: "$payments.details.spent",
initialValue: [],
in: { $concatArrays: ["$$value", "$$this"] }
}
}
}
},
{ $set: { spent_total: { $sum: "$spent_total" } } },
{ $match: { "spent_total": { $gte: 100 } } }
])Mongo 游乐场
更新：
payment_system的过滤器要长一点。你必须要$unwind和$group
db.collection.aggregate([
{
$set: {
payments: {
$filter: {
input: "$payments",
cond: { $gte: ["$$this.date", new Date("2019-01-02")] }
}
}
}
},
{ $unwind: "$payments" },
{
$set: {
"payments.details": {
$filter: {
input: "$payments.details",
cond: { $in: ["$$this.payment_system", ["A", "C"]] }
},
},
}
},
{
$group: {
_id: { _id: "$_id", name: "$name", },
payments: { $push: "$payments" }
}
},
{
$set: {
spent_total: {
$reduce: {
input: "$payments.details.spent",
initialValue: [],
in: { $concatArrays: ["$$value", "$$this"] }
}
}
}
},
{ $set: { spent_total: { $sum: "$spent_total" } } },
{ $match: { "spent_total": { $gte: 100 } } },
{ // just some cosmetic
$project: {
_id: "$_id._id",
name: "$_id.name",
payments: 1
}
}
])不能像db.collection.updateMany({}, [<the aggregation pipeline from above>])一样更新集合，因为它包含$unwind和$group。但是，您可以使$lookup或$out将整个结果保存到新集合中。
如果您需要对每个payment_system分别进行汇总，那么请尝试：
db.collection.aggregate([
{
$set: {
payments: {
$filter: {
input: "$payments",
cond: { $gte: ["$$this.date", new Date("2019-01-01")] }
}
}
}
},
{ $unwind: "$payments" },
{
$set: {
"payments.details": {
$filter: {
input: "$payments.details",
cond: { $in: ["$$this.payment_system", ["A", "B","C"]] }
},
},
}
},
{ $unwind: "$payments.details" },
{
$group: {
_id: {
_id: "$_id",
name: "$name",
payments: "$payments.details.payment_system"
},
spent_total: { $sum: "$payments.details.spent" }
}
},
{ $match: { "spent_total": { $gte: 100 } } },
{
$project: {
_id: "$_id._id",
name: "$_id.name",
payments: "$_id.payments",
spent_total: 1
}
}
])页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60149986
复制相关文章
相似问题
腾讯云开发者
