如何在MongoDB中查询两个具有"one-to-many"关系的集合？

Question

我们有两个系列:">houses";事件和">"。houses集合中的每个文档都包含一个">事件";id的数组的字段指向事件(a ">一对多)的";关系)。

数组可以为空。房子,";模式:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const HouseSchema = new Schema({
name: String,
district: String,
locality: String,
date: {
type: Date,
default: Date.now
},
events: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'Event'
}]
});
module.exports = mongoose.model('House', HouseSchema);

<<p>,事件/strong>";模式:

const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const EventSchema = new Schema({
event: String,
details: String,
date: {
type: Date,
default: Date.now
},
house: {
type: mongoose.Schema.Types.ObjectId,
ref: 'House'
}
});
module.exports = mongoose.model('Event', EventSchema);

我需要查询两个"房子"one_answers";events"找到那些"房子"参考"事件";符合所有的标准。我将给出几个查询选项。

参数只能从"house "查询。假设"房子"里有两个医生满足条件{"locality": "Facebook"}的集合。结果应该是这样的:

[
{
"_id": "601bae8e26ed00591d571dbe",
"name": "Facebook testing department",
"district": "Los-Angeles",
"locality": "Facebook",
"events": [
{
"_id": "601bae8e26ed00591d571dbf",
"event": "Testing",
"details": "Testing software for production",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
{
"_id": "601bae8e26ed00591d571dbc",
"name": "Facebook office",
"district": "Los-Angeles",
"locality": "Facebook",
"events": [
{
"_id": "601bae8e26ed00591d571dbd",
"event": "Conference",
"details": "Developers conference about 12345",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
];

只对"事件"进行带参数的查询。假设在"事件"中有两条记录;满足条件{"event": "Conference"}的集合。结果是:

[
{
"_id": "601bae8e26ed00591d571dbc",
"name": "Facebook office",
"district": "Los-Angeles",
"locality": "Facebook",
"events": [
{
"_id": "601bae8e26ed00591d571dbd",
"event": "Conference",
"details": "Developers conference about 12345",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
{
"_id": "601bae8e26ed00591d571dba",
"name": "Oxford",
"district": "London",
"locality": "Oxford",
"events": [
{
"_id": "601bae8e26ed00591d571dbb",
"event": "Conference",
"details": "About something",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
];

包含两个"house "参数的查询和";events"。如果我们的查询条件是{"locality": "Facebook", "event": "Conference"}，那么结果应该是这样的:

[  
{
"_id": "601bae8e26ed00591d571dbc",
"name": "Facebook office",
"district": "Los-Angeles",
"locality": "Facebook",
"events": [
{
"_id": "601bae8e26ed00591d571dbd",
"event": "Conference",
"details": "Developers conference about 12345",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
];

我遇到过类似的情况，有必要使查询比平时更复杂。但是这里我们需要根据参数对一个或两个集合进行查询，因此，我们应该得到"house"。使用嵌套的事件"对应于查询参数。我不知道MongoDB聚合我很感激你的帮助。

Answer 1

• 声明输入

let locality = req.body.locality;
let event = req.body.event;

• 初始化聚合管道(将HousesModel替换为reql模型名称)

let p = HousesModel.aggregate();

• locality检查条件

if (locality && locality.trim()) p.match({ locality: locality });

• $lookupwith pipeline，
声明events数组为let
• 表达式中的匹配事件条件
• 检查条件，如果事件搜索可用，然后放入条件

p.lookup({
from: "events", // replace your actual collection name if this is wrong
let: { events: "$events" },
pipeline: [
{
$match: {
$and: [
{ $expr: { $in: ["$_id", "$$events"] } },
(event && event.trim() ? { event: event } : {})         
]
}
}
],
as: "events"
});

• 确保事件不为空

p.match({ events: { $ne: [] } });

• 执行上面的管道

let result = await p.exec();

游乐场

按顺序组合以上代码并测试