SO i have an structure of answer from mongodb like this...

[
{
  "_id": {
    "$oid": "636e685d1e1b4aa82f7e87c7"
  },
  "userId": {
    "$oid": "6369b17b11e02557349d8e37"
  },
  "formId": {
    "$oid": "6361d3bf914c7e5a21efa190"
  },
  "title": "Order Kerja Produksi (KSNI)",
  "username": "22000510",
  "date": "2022-11-11",
  "answer": {
    "Tanggal": "2022-11-11",
    "Plant": "Majalengka",
    "NIK Foreman Packaging": "22000510",
    "NIK Foreman Processing": "22000266",
    "Shift": [
      "Siang"
    ],
    "Regu": [
      "R3"
    ],
    "Business Unit": [
      "Wafer Flat"
    ],
    "Line": "A02006",
    "SKU": "320453",
    "BS Kertas/Layer (pcs)": "0",
    "Off (Menit)": "0"
  },
  "createdAt": 1668180061,
  "updatedAt": 1668180061,
  "__v": 0,
  "position": "Foreman",
  "department": "Production",
  "SPV": "HANAFIAH",
  "fullname": "LIANA DEWI NURATIKAH",
  "status": "verified"
},{
  "_id": {
    "$oid": "636e699c1e1b4aa82f7e87cb"
  },
  "userId": {
    "$oid": "6369b17b11e02557349d8e37"
  },
  "formId": {
    "$oid": "6361d3bf914c7e5a21efa190"
  },
  "title": "Order Kerja Produksi (KSNI)",
  "username": "22000510",
  "date": "2022-11-11",
  "answer": {
    "Tanggal": "2022-11-11",
    "Plant": "Majalengka",
    "NIK Foreman Packaging": "22000510",
    "NIK Foreman Processing": "22000266",
    "Shift": [
      "Siang"
    ],
    "Regu": [
      "R3"
    ],
    "Business Unit": [
      "Wafer Flat"
    ],
    "Line": "A02009",
    "SKU": "320453",
    "Issue": "MPP",
    "Penjelasan": "Mpp kurang"
  },
  "createdAt": 1668180380,
  "updatedAt": 1668180380,
  "__v": 0,
  "department": "Production",
  "position": "Foreman",
  "SPV": "HANAFIAH",
  "fullname": "LIANA DEWI NURATIKAH",
  "status": "verified"
},{
  "_id": {
    "$oid": "636e6ace1e1b4aa82f7e87cf"
  },
  "userId": {
    "$oid": "6369b17b11e02557349d8e37"
  },
  "formId": {
    "$oid": "6361d3bf914c7e5a21efa190"
  },
  "title": "Order Kerja Produksi (KSNI)",
  "username": "22000510",
  "date": "2022-11-11",
  "answer": {
    "Tanggal": "2022-11-11",
    "Plant": "Majalengka",
    "Issue": "Bearing conveyor masema",
    "Penjelasan": "ganti bearing"
  },
]

i used to group it by the date field, but now i have to group it based on field "Tanggal" inside the "answer" field, here is my try:

dataAnaylitics = await Answer.aggregate([
          // $match stage
          {
            $match: {
              date: {
                $gte: date1,
                $lte: date2,
              },
            },
          },
          {
            $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 } }],
            },
          },
          {
            $lookup: {
              from: "users",
              localField: "_id.username",
              foreignField: "NIK",
              as: "userInformation",
              pipeline: [{ $project: { _id: 0, userInformation: 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] },
              },
              userInformation: {
                $first: {
                  $arrayElemAt: ["$userInformation.userInformation", 0],
                },
              },
            },
          },
          {
            $project: {
              _id: 0,
              username: "$_id.username",
              title: "$_id.title",
              position: 1,
              department: 1,
              dates: 1,
              fullname: 1,
              userInformation: 1,
            },
          },
          {
            $replaceRoot: {
              newRoot: {
                $mergeObjects: [
                  "$$ROOT",
                  {
                    $arrayToObject: "$dates",
                  },
                ],
              },
            },
          },
          {
            $unset: "dates",
          },
        ]);

is that possible for me to group it based on "Tanggal" inside the answer field using that query i have made before, i try so find an answer on my problem through google, but got no clue on that..., any help on that or maybe some reference on how to match it inside an object

CodePudding user response：

Just replaing replace date by the value answer.Tanggal should do the trick.

dataAnaylitics = await Answer.aggregate([
          // $match stage
          {
            $match: {
              "answer.Tanggal": {
                $gte: date1,
                $lte: date2,
              },
            },
          },
          {
            $group: {
              _id: {
                username: "$username",
                title: "$title",
                date: "$answer.Tanggal",
              },
              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 } }],
            },
          },
          {
            $lookup: {
              from: "users",
              localField: "_id.username",
              foreignField: "NIK",
              as: "userInformation",
              pipeline: [{ $project: { _id: 0, userInformation: 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] },
              },
              userInformation: {
                $first: {
                  $arrayElemAt: ["$userInformation.userInformation", 0],
                },
              },
            },
          },
          {
            $project: {
              _id: 0,
              username: "$_id.username",
              title: "$_id.title",
              position: 1,
              department: 1,
              dates: 1,
              fullname: 1,
              userInformation: 1,
            },
          },
          {
            $replaceRoot: {
              newRoot: {
                $mergeObjects: [
                  "$$ROOT",
                  {
                    $arrayToObject: "$dates",
                  },
                ],
              },
            },
          },
          {
            $unset: "dates",
          },
        ]);