So I have a MongoDB document structure as below:

[
  {
    "_id": {
      "$oid": "6374485a942585decaadb2bc"
    },
    "userId": {
      "$oid": "6368e40d1f58fd76efb27957"
    },
    "formId": {
      "$oid": "6361c5aaf7a02c177ebebb27"
    },
    "title": "Test",
    "username": "MT220047",
    "position": "Management Trainee",
    "department": "Logistic",
    "date": "2022-11-15",
    "answer": {
      "Email": "[email protected]",
      "Dropdown": "New Option",
      "Test": [
        "New Option"
      ],
      "Radio": "3",
      "Date": "2022-11-01",
      "Time": "08:40"
    },
    "createdAt": 1668565082,
    "updatedAt": 1668565082,
    "__v": 0
  },
  {
    "_id": {
      "$oid": "63744886942585decaadb2cb"
    },
    "userId": {
      "$oid": "6368e40d1f58fd76efb27957"
    },
    "formId": {
      "$oid": "6361c5aaf7a02c177ebebb27"
    },
    "title": "Test",
    "username": "MT220047",
    "position": "Management Trainee",
    "department": "Logistic",
    "date": "2022-11-16",
    "answer": {
      "Test": [
        "New Option",
        "C"
      ],
      "Email": "[email protected]",
      "Dropdown": "New Option",
      "Radio": "3",
      "Date": "2022-11-14",
      "Time": "21:41"
    },
    "createdAt": 1668565126,
    "updatedAt": 1668565126,
    "__v": 0
  },
  {
    "_id": {
      "$oid": "63744fa6942585decaadb2f5"
    },
    "userId": {
      "$oid": "6368e40d1f58fd76efb27957"
    },
    "formId": {
      "$oid": "6361c5aaf7a02c177ebebb27"
    },
    "title": "Test",
    "username": "MT220047",
    "position": "Management Trainee",
    "department": "Logistic",
    "date": "2022-11-16",
    "answer": {
      "Test": [
        "New Option",
        "C"
      ],
      "Dropdown": "!",
      "Email": "[email protected]",
      "Radio": "3",
      "Date": "2022-11-01",
      "Time": "15:07"
    },
    "createdAt": 1668566950,
    "updatedAt": 1668566950,
    "__v": 0
  },
  {
    "_id": {
      "$oid": "63748d37457e68b036e0dd34"
    },
    "userId": {
      "$oid": "6368e41cb7bcf09f8ffb1358"
    },
    "formId": {
      "$oid": "6361c5aaf7a02c177ebebb27"
    },
    "title": "Test",
    "username": "17000691",
    "position": "Foreman",
    "department": "Production",
    "date": "2022-11-16",
    "answer": {
      "Test": [
        "New Option",
        "A",
        "C"
      ],
      "Email": "[email protected]",
      "Dropdown": "New Option",
      "Radio": "3",
      "Date": "2022-11-07",
      "Time": "19:39"
    },
    "createdAt": 1668582711,
    "updatedAt": 1668582711,
    "__v": 0
  },
  {
    "_id": {
      "$oid": "63748efb0d7b3e3abf2100c2"
    },
    "userId": {
      "$oid": "6368e40d1f58fd76efb27957"
    },
    "formId": {
      "$oid": "6361e0820cb1e1b72ac99621"
    },
    "title": "Untitled Form1",
    "username": "MT220047",
    "position": "Management Trainee",
    "department": "Logistic",
    "date": "2022-11-16",
    "createdAt": 1668583163,
    "updatedAt": 1668583163,
    "__v": 0
  }
]

I try to and pivoting using some query, to get the answer I want, but it turns out that it doesn't show up as I hope, below is the query I used and the answer I hope:

const dataAnaylitics = await Answer.aggregate([
        {
          $match: {
            $and: [
              {
                date: {
                  $gte: date1,
                  $lte: date2,
                },
              },
            ],
          },
        },
        {
          $group: {
            _id: "$username",

            latestAnswer: {
              $push: {
                title: "$title",
                date: "$date",
                position: "$position",
                department: "$department",
                username: "$username",
              },
            },
          },
        },
      ]);

I want to get an answer like this. The data should have been grouped:

[
  {
    "username": "MT220047",
    "title": "Test",
    "position": "Management Trainee",
    "department": "Logistic",
    "2022-11-15": 1,
    "2022-11-16": 2
  },
  {
    "username": "MT220047",
    "title": "UntitledForm1",
    "position": "Management Trainee",
    "department": "Logistic",
    "2022-11-16": 1
  },
  {
    "username": "17000691",
    "title": "Test",
    "position": "Foreman",
    "department": "Production",
    "2022-11-16": 1,

  }
]

Is that a possible way to get that or do I have to do some basic JavaScript on it?

CodePudding user response：

1. $group - Group by username, title, and date. Perform a count of the documents. Extract the first value of department and position.

2. $group - Group by username and title. Add date key and value as { k: "", v: 0 } object into the dates array. Extract the first value of department and position.

3. $project - Decorate the output document.

4. $replaceRoot - Replace the input document by merging the current document with the document converted from the dates array to object via $arrayToObject.

5. $unset - Remove the dates field.

const dataAnaylitics = await Answer.aggregate([
  // $match stage
  {
    $group: {
      _id: {
        username: "$username",
        title: "$title",
        date: "$date"
      },
      count: {
        $sum: 1
      },
      position: {
        $first: "$position"
      },
      department: {
        $first: "$department"
      }
    }
  },
  {
    $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"
  }
])

Demo @ Mongo Playground