所以我有一個 MongoDB 檔案結構如下：
[
{
"_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
}
]
我嘗試使用一些查詢進行旋轉，以獲得我想要的答案，但事實證明它并沒有像我希望的那樣出現，下面是我使用的查詢和我希望的答案：
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",
},
},
},
},
]);
我想得到這樣的答案。資料應該已經分組：
[
{
"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,
}
]
這是一種可能的方法嗎？還是我必須在上面做一些基本的 JavaScript？
uj5u.com熱心網友回復：
$groupusername- 按、title和分組date。對檔案進行計數。department提取和的第一個值position。$group- 分組依據username和title。將日期鍵和值作為{ k: "", v: 0 }物件添加到dates陣列中。department提取和的第一個值position。$project- 修飾輸出檔案。$replaceRoot- 通過將當前檔案與從dates陣列轉換為物件的檔案合并來替換輸入檔案$arrayToObject。$unset- 洗掉dates欄位。
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"
}
])
演示 @ Mongo 游樂場
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/534639.html