mongodb中对象数组的条件查找

Question

我有两个集合，一个是产品另一个是orders我想在产品中写入聚合，以获得匹配的订单。

产品

[{
"id": "738097c4-5c52-11eb-ae93-0242ac130002",
"title": "test product",
"description": "Amet dolor justo erat sadipscing at sed sit et labore..",
"combos": ["738097c4", "738097c5"]
},
{
"id": "923097c4-5c52-11eb-ae93-0242ac1300cj2",
"title": "test product 2",
"description": "Acjhz cjzh ouhcio cho ",
"combos": ["94563097c4", "84097e5"]
}]

<<p>订单/strong>

[
{
"id": "ce943752-7040-4926-9c1a-350633f4331f",
"items": [
{
"itemId": "738097c4-5c52-11eb-ae93-0242ac130002",
"type": "product",
"expiry": "2021-10-10"
},
{
"itemId": "738097c4",
"type": "combo",
"expiry": "2021-12-10"
}
]
},
{
"id": "33c59dc4-c443-45a7-99c2-caba98f6d107",
"items": [
{
"itemId": "738097c4-5c52-11eb-ae93-0242ac130002",
"type": "product",
"expiry": "2022-11-10"
},
{
"itemId": "738097c5",
"type": "combo",
"expiry": "2020-10-10"
}
]
}
]

预期输出

产品

[{
"id": "738097c4-5c52-11eb-ae93-0242ac130002",
"title": "test product",
"description": "Amet dolor justo erat sadipscing at sed sit et labore..",
"combos": ["738097c4", "738097c5"],
"orders": [
{
"id": "ce943752-7040-4926-9c1a-350633f4331f",
"items": [
{
"itemId": "738097c4-5c52-11eb-ae93-0242ac130002",
"type": "product",
"expiry": "2021-10-10"
},
{
"itemId": "738097c4",
"type": "combo",
"expiry": "2021-12-10"
}]
}].
},
{
"id": "923097c4-5c52-11eb-ae93-0242ac1300cj2",
"title": "test product 2",
"description": "Acjhz cjzh ouhcio cho ",
"combos": ["94563097c4", "84097e5"],
"orders:: []
}]

<<p>

匹配条件/strong>Orders.items.expiry大于当前时间

和

Orders.items.itemIdproduct .id

或

Orders.items.itemIdproducts.combos)

请帮我找到解决方案

Answer 1

可以使用$lookup来连接集合

• $filter过滤出匹配日期
• $lookup加入集合，我使用不相关的子查询

脚本为

db.Order.aggregate(
[{$addFields: {
items: {
$filter:{
input:"$items",
cond:{
$gt:[{$toDate:"$$this.expiry"},new Date()]
}
}
}
}}, {$lookup: {
from: 'Products',
let:{itemIds:"$items.itemId"},
pipeline:[
{
$match:{
$expr:{
$or:[
{$in:["$id","$$itemIds"]},
{$in:["$combos","$$itemIds"]}
]
}
}
}
],
as: 'join'
}}]
)

更新1

因为您需要product

的输出

[{$lookup: {
from: 'Orders',
let:{pId:"$id",comboArray:"$combos"},
pipeline:[
{$addFields: {
items: {
$filter:{
input:"$items",
cond:{
$gt:[{$toDate:"$$this.expiry"},new Date()]
}
}
}
}},
{
$unwind:"$items"
},
{
$match:{
$expr:{
$or:[
{$eq:["$$pId","$items.itemId"]},
{$in:["$items.itemId","$$comboArray"]}
]
}
}
},
{
$replaceRoot:{
newRoot:"$items"
}
}
],
as: 'orders'
}}]

Working Mongo playground