蒙戈数据库复杂查询

Question

我需要一些关于MongoDB的帮助。 我有如下架构和数据库： `

结核病：

User:
{
name: { type: String }
image: { type: String }
city: { type: String }
address: [{
index: { type: Number }
district_id: { type: Schema.ObjectId, ref: 'District' }
}]
}

Tb2：

District:{
name: { type: String }
code: { type: String }}

示例数据库：

User: [{
_id: '1234'
name: 'Jacky',
image: '',
city: 'Da Nang',
address: [
{
index: 1,
district_id: '12345'
},
{
index: 2,
district_id: '123456'
},
{
index: 3,
district_id: '1234567'
}
]
}]

District: [
{
_id: '12345',
name: 'Hai Chau',
code: 12
},
{
_id: '123455',
name: 'Lien CHieu',
code: 13
},
{
_id: '1234567',
name: 'Cam Le',
code: 14
},
{
_id: '12345678',
name: 'Son Tra',
code: 15
}
]

如何通过两个选项(disctrict.name&&索引(选择用户，例如 district.name='Lien Chieu' && address.index> 1。

Answer 1

你可以试试下面的查询：

db.District.aggregate([
/** filter required doc from district Coll */
{ $match: { name: "Lien CHieu" } },
{
$lookup: {
from: "User",
let: { id: "$_id" },
pipeline: [
/** Basic filter to get matched doc from User Coll */
{ $match: { $expr: { $in: ["$$id", "$address.district_id"] } } },
/** check for criteria */
{
$addFields: {
mappingField: {
$map: {
input: "$address", as: "each", in: {
$and: [{ $gt: ["$$each.index", 1] }, { $eq: ["$$id", "$$each.district_id"] }]
}
}
}
}
},
/** filter required doc from above which matches our criteria & remove added field */
{ $match: { mappingField: true } }, { $project: { mappingField: 0 } }
],
as: "userdata"
}
}
])

测试：MongoDB-Playground