聚合中匹配的Mongodb查找返回一个空数组

Question

这是我的用户文档

{
"_id":"02a33b9a-284c-4869-885e-d46981fdd679",
"context":{
"email":"[e-mail]",
"firstName":"John",
"lastName":"Smith",
"company":[
"e2467c93-114b-4613-a842-f311a8c537b3"
],
},
}

和公司文档

{
"_id":"e2467c93-114b-4613-a842-f311a8c537b3",
"context":{
"name":"Coca Cola",
"image":"someimage",
},
};

这是我对用户的查询

let users = await Persons.aggregate(
[{$project:
{
name: {$concat: ['$context.firstName', ' ', '$context.lastName']},
companyId: {$arrayElemAt: ['$context.company', 0]}}
},
{$match: {name: searchRegExp}},
{$lookup: {from: 'companies', let: {company_id: {$arrayElemAt: ['$context.company', 0]}}, pipeline:
[
{
$match: {
$expr: {
$eq: ['$_id', '$$company_id']
}
}
},
{
$project: {name: '$context.name'}
}
],
as: 'company'}}
]).toArray()

当我运行这个查询时，我得到的公司字段是一个空数组，我在这里做错了什么？

Answer 1

您的第一个管道阶段$project只输出_id、name和companyId，因此当您试图在$lookup中引用$context.company时，会有一个空值。您可以使用$addFields:

{
$addFields: {
name: {
$concat: [
"$context.firstName",
" ",
"$context.lastName"
]
},
companyId: {
$arrayElemAt: [
"$context.company",
0
]
}
}
}

Mongo游乐场

Answer 2

添加字段companyId: {$arrayElemAt: ['$context.company', 0]}}时，可以使用$lookup的简单版本。无需设置两次，一次作为companyId: ...，另一次在let: {company_id: ...}中

db.user.aggregate([
{
$addFields: {
name: { $concat: ["$context.firstName", " ", "$context.lastName"] },
companyId: { $arrayElemAt: ["$context.company", 0] } 
}
},
{
$lookup: {
from: "company",
localField: "companyId",
foreignField: "_id",
as: "company"
}
}
])