so i have document for users with this structure in JSON format:

[
  {
    "_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
  },
  
]

it try to lookitup using mongodb $lookup to get the fullname by joining using the NIK as the foreignnkey,here is what i have try:

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",
        },
      ]);

but the result doesnt returning the fullname field, is there is something wrong with my code? i seek for documentation and already follow the step

CodePudding user response：

In your group stage, since you are grouping based on username, the resulting document will have _id.username as the field. Use this field as localField in your lookup.

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

CodePudding user response：

i have fix it, hope it will helps other..

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",
        },
      ]);