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": "john@nabatisnack.com",
"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": "john@nabatisnack.com",
"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": "john@nabatisnack.com",
"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": "john@dose.com",
"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?
$group - Group by username, title, and date. Perform a count of the documents. Extract the first value of department and position.
$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.
$project - Decorate the output document.
$replaceRoot - Replace the input document by merging the current document with the document converted from the dates array to object via $arrayToObject.
$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"
}
])