我正在嘗試在 mongodb 中實作嵌套組查詢，但我遇到了困難。如何在此處按日期和活動 ID 對資料進行分組？每個campaign_id 都應包含一個嵌套的廣告素材陣列，其中包含每個廣告素材的資料（查看次數和點擊次數）。
資料如下所示： 資料：
[
{
"_id": ObjectId("61e3b88d3f14fb87161651c7"),
"campaign_id": 41,
"creative_id": 143,
"date": ISODate("2022-01-16T00:00:00Z"),
"views": 1
},
{
"_id": ObjectId("61e3b8cd3f14fb87161651ef"),
"campaign_id": 41,
"creative_id": 143,
"date": ISODate("2022-01-16T00:00:00Z"),
"views": 7070,
"clicks": 241
},
{
"_id": ObjectId("61e3b8cd3f14fb87161651f2"),
"campaign_id": 41,
"creative_id": 144,
"date": ISODate("2022-01-16T00:00:00Z"),
"views": 3474,
"clicks": 52
},
{
"_id": ObjectId("61e3b8cd3f14fb87161651f4"),
"campaign_id": 41,
"creative_id": 146,
"date": ISODate("2022-01-16T00:00:00Z"),
"views": 3595,
"clicks": 52
},
{
"_id": ObjectId("61e3b8cd3f14fb87161651f6"),
"campaign_id": 41,
"creative_id": 145,
"date": ISODate("2022-01-16T00:00:00Z"),
"views": 3561,
"clicks": 227
},
{
"_id": ObjectId("61e4b18a3f14fb871616d8b0"),
"campaign_id": 41,
"creative_id": 143,
"date": ISODate("2022-01-17T00:00:00Z"),
"views": 9528,
"clicks": 104
},
{
"_id": ObjectId("61e4b18a3f14fb871616d8b2"),
"campaign_id": 41,
"creative_id": 145,
"date": ISODate("2022-01-17T00:00:00Z"),
"views": 4699,
"clicks": 100
},
{
"_id": ObjectId("61e4b18a3f14fb871616d8b5"),
"campaign_id": 41,
"creative_id": 146,
"date": ISODate("2022-01-17T00:00:00Z"),
"views": 4759,
"clicks": 36
},
{
"_id": ObjectId("61e4b18e3f14fb871616d8ca"),
"campaign_id": 41,
"creative_id": 144,
"date": ISODate("2022-01-17T00:00:00Z"),
"views": 4822,
"clicks": 27
}
]
這是分組后的預期結果： 預期結果：
{
"_id": {
"campaign": 955,
"date": ISODate("2022-01-16T00:00:00Z")
},
"creative": [
143: {clicks: XXXX, views:XXXX}
146: {clicks: XXXX, views:XXXX},
142: {clicks: XXXX, views:XXXX},
...
]
},
https://mongoplayground.net/p/oZNxQNb250R
uj5u.com熱心網友回復：
你可以這樣做：
db.collection.aggregate([
{
"$sort": {
"date": 1
}
},
{
"$group": {
"_id": {
date: "$date",
campaign: "$campaign_id",
creative_id: "$creative_id"
},
views: {
$sum: "$views"
},
clicks: {
$sum: "$clicks"
}
}
},
{
"$group": {
"_id": {
date: "$_id.date",
campaign: "$_id.campaign"
},
data: {
$push: {
k: {
"$toString": "$_id.creative_id"
},
v: {
views: "$views",
clicks: "$clicks"
}
}
}
}
},
{
$project: {
_id: 1,
creative: {
"$arrayToObject": "$data"
}
}
}
])
蒙戈游樂場
請注意，我添加了一個額外的$group階段，這是為了分組creative_id并總結它的所有視圖和點擊。如果這個階段是多余的，你可以消除這個階段，但它可以作業。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/414136.html
標籤：