MongoDB查找-使用$lookup

Question

所以我有一个JSON格式的用户文档:

[
{
"_id": {
"$oid": "6369aeb83ce0f8168520f42f"
},
"fullname": "Jokona",
"password": "$2b$10$MUAe7XIc/xtJTGVh/y1DeuShCARbwxCSejUbHaqIPZfjekNrn0.Yy",
"NIK": "MT220047",
"status": "active",
"department": "Logistic",
"position": "Management Trainee",
"Group_Shift": "Non Shift",
"role": "admin",
"createdAt": 1667870392,
"updatedAt": 1668564835,
"__v": 0
},
{
"_id": {
"$oid": "6369b17b11e02557349d8de5"
},
"fullname": "Warana",
"password": "$2b$10$0xaqz5V8bar/osWmsCiofet5bY10.ORn8Vme3QC7Dh0HwLHwYOm3a",
"NIK": "17000691",
"status": "active",
"department": "Production",
"position": "Foreman",
"Group_Shift": "R1",
"role": "user",
"__v": 0,
"createdAt": 1667871099,
"updatedAt": 1668496775
},

]

它尝试使用mongodb $lookup通过使用NIK作为外键加入来获取全名，这就是我所尝试的:

const dataAnaylitics = await Answer.aggregate([
// $match stage
{
$group: {
_id: {
username: "$username",
title: "$title",
date: "$date",
},
count: {
$sum: 1,
},
position: {
$first: "$position",
},
department: {
$first: "$department",
},
},
},
{
$lookup: {
from: "users",
localField: "username",
foreignField: "NIK",
as: "fullname",
pipeline: [{ $project: { fullname: 0 } }],
},
},
{
$group: {
_id: {
username: "$_id.username",
title: "$_id.title",
},
dates: {
$push: {
k: "$_id.date",
v: "$count",
},
},
position: {
$first: "$position",
},
department: {
$first: "$department",
},
},
},
{
$project: {
_id: 0,
username: "$_id.username",
title: "$_id.title",
position: 1,
department: 1,
dates: 1,
},
},
{
$replaceRoot: {
newRoot: {
$mergeObjects: [
"$$ROOT",
{
$arrayToObject: "$dates",
},
],
},
},
},
{
$unset: "dates",
},
]);

但结果不返回全名字段，是我的代码有问题吗?我寻找文档，并且已经按照

步骤进行了操作。

Answer 1

在您的分组阶段，由于您是基于用户名进行分组的，因此结果文档将以_id.username作为字段。在查找中使用此字段作为localField。

{
$lookup: {
from: "users",
localField: "_id.username",
foreignField: "NIK",
as: "fullname",
pipeline: [{ $project: { fullname: 0 } }],
}

Answer 2

我已经把它修好了，希望对其他人有所帮助。

const dataAnaylitics = await Answer.aggregate([
// $match stage
{
$group: {
_id: {
username: "$username",
title: "$title",
date: "$date",
},
count: {
$sum: 1,
},
position: {
$first: "$position",
},
department: {
$first: "$department",
},
},
},
{
$lookup: {
from: "users",
localField: "_id.username",
foreignField: "NIK",
as: "fullname",
pipeline: [{ $project: { _id: 0, fullname: 1 } }],
},
},
{
$group: {
_id: {
username: "$_id.username",
title: "$_id.title",
},
dates: {
$push: {
k: "$_id.date",
v: "$count",
},
},
position: {
$first: "$position",
},
department: {
$first: "$department",
},
fullname: {
$first: { $arrayElemAt: ["$fullname.fullname", 0] },
},
},
},
{
$project: {
_id: 0,
username: "$_id.username",
title: "$_id.title",
position: 1,
department: 1,
dates: 1,
fullname: 1,
},
},
{
$replaceRoot: {
newRoot: {
$mergeObjects: [
"$$ROOT",
{
$arrayToObject: "$dates",
},
],
},
},
},
{
$unset: "dates",
},
]);