在聚合投影MongoDB中将多个子文档合并为数组 [英] Merge multiple subdocuments as an array in aggregation projection MongoDB
问题描述
我在这里试图做的是查询和合并多个子文档，以数组作为回报。我认为聚合框架是可行的方法，但我似乎无法完全正确。
What I am trying to do here is querying and merging multiple sub-documents in return as an array. I think the aggregation framework is the way to go, but I can't seem to get it exactly right.
这里是我的收藏集，名为 visitors ：
Here is my collection called visitors:
{
"_id": ObjectId("57dc5c2b7463d336ec3fff8c"),
"username": "Bob",
"fullname": "Bobby",
"activities":
{ "visits" : [
{
"_id": ObjectId("57dc674e4208b12fd4a52a3d"),
"date": ISODate("2016-09-18T08:00:00.000Z"),
"location" : "Jakarta",
},
{
"_id": ObjectId("57dd3795c13c5e2b7484ea4b"),
"date": ISODate("2016-09-17T08:00:00.000Z"),
"location" : "Denpasar",
}
],
"purchases" : [
{
"_id": ObjectId("57dc4769c0f09317282b3f92"),
"date": ISODate("2016-09-17T07:30:00.000Z"),
"product_name" : "Shirt",
"price": 125000
},{
"_id": ObjectId("57dbfdc6be9dcf1e7c4a1751"),
"date": ISODate("2016-09-18T08:30:00.000Z"),
"product_name" : "Shoes",
"price": 150000
}
]},
}
}
这是我想要的文档，并尝试通过聚合实现，这是这样的，条件是我可以按日期排序，使用 limit 和 skip 。
And here is the document that I want and try to achieve with aggregation is this, with condition that i can sort them by date, use limit and skip.
{
"_id": ObjectId("57dc5c2b7463d336ec3fff8c"),
"activities": [
{
"activity_type": "purchases",
"_id": ObjectId("57dbfdc6be9dcf1e7c4a1751"),
"date": ISODate("2016-09-18T08:30:00.000Z"),
"product_name" : "Shoes",
"price": 150000
},{
"activity_type": "visits",
"visits_id": ObjectId("57dc674e4208b12fd4a52a3d"),
"date": ISODate("2016-09-18T08:00:00.000Z"),
"location" : "Jakarta",
},{
"activity_type": "visits",
"visits_id": ObjectId("57dd3795c13c5e2b7484ea4b"),
"date": ISODate("2016-09-17T08:00:00.000Z"),
"location" : "Denpasar",
},{
"activity_type": "purchases",
"date": ISODate("2016-09-17T07:30:00.000Z"),
"product_name" : "Shirt",
"price": 125000
}]
}
我一直在尝试使用此聚合
I've been trying to do this with this aggregation
db.visitors.aggregate([{
$match: { _id: ObjectId("57dc5c2b7463d336ec3fff8c") } },
{
$group: {
_id: "$_id",
visits: {
"$addToSet": "$activities.visits"
},
purchases: {
"$addToSet": "$activities.purchases"
}
}
}])
但是我不明白y我想要的是，而是按其类型对它们进行分组，而我什至不能使用 skip 和 limit （似乎只是跳过并限制访问者人数）。
But I don't get exactly what i want, it was instead just group them by their type, and I can't even use skip and limit (seems to just skip and limit visitors number).
{
"_id": ObjectId("57dc5c2b7463d336ec3fff8c"),
"visits": [
[
{
"_id": ObjectId("57dc674e4208b12fd4a52a3d"),
"date": ISODate("2016-09-18T08:00:00.000Z"),
"location" : "Jakarta",
},
{
"_id": ObjectId("57dd3795c13c5e2b7484ea4b"),
"date": ISODate("2016-09-17T08:00:00.000Z"),
"location" : "Denpasar",
}
]
],
"news": [
[
{
"_id": ObjectId("57dc4769c0f09317282b3f92"),
"date": ISODate("2016-09-17T07:30:00.000Z"),
"product_name" : "Shirt",
"price": 125000
},{
"_id": ObjectId("57dbfdc6be9dcf1e7c4a1751"),
"date": ISODate("2016-09-18T08:30:00.000Z"),
"product_name" : "Shoes",
"price": 150000
}
]
]
}
尝试使用展开，
db.visitors.aggregate([
{ $match: { _id: ObjectId("57dc5c2b7463d336ec3fff8c") } },
{ $unwind: '$activities.visits' },
{ $unwind: '$activities.purchases' },
{ $project: {
_id: 0,
//visits
"visits_id": "$activities.visits._id",
"visits_date": "$activities.visits.date",
"visits_location" : "$activities.visits.location"
//purchases
"purchases_id": "$activities.purchases._id",
"purchases_date": "$activities.purchases.date",
"purchases_product_name": "$activities.purchases.product_name",
"purchases_price": "$activities.purchases.price",
}
}
])
.skip(0)
.limit(2)
但似乎在每个索引处合并了不同类型的文档
but it seems to merge the documents with different type at each index
{
"_id": ObjectId("57dc5c2b7463d336ec3fff8c"),
"activities": [
{
"purchases_id": ObjectId("57dbfdc6be9dcf1e7c4a1751"),
"purchases_date": ISODate("2016-09-18T08:30:00.000Z"),
"purchases_product_name" : "Shoes",
"purchases_price": 150000
"visits_id": ObjectId("57dc674e4208b12fd4a52a3d"),
"visits_date": ISODate("2016-09-18T08:00:00.000Z"),
"visits_location" : "Jakarta",
},{
"purchases_id": ObjectId("57dc4769c0f09317282b3f92"),
"purchases_date": ISODate("2016-09-17T07:30:00.000Z"),
"purchases_product_name" : "Shirt",
"purchases_price": 125000
"visits_id": ObjectId("57dd3795c13c5e2b7484ea4b"),
"visits_date": ISODate("2016-09-17T08:00:00.000Z"),
"visits_location" : "Denpasar",
}]
}
是否可以对当前文档执行此操作？还是应该更改文档结构？
Is it possible to do this with my current document? Or should I change my document structure?
更新已解决
我结束了在数组中的每个子文档中添加 activity_type ，并使用 $ setUnion 合并多个数组作为数组来解决此问题，并使用 $ slice 限制和跳过数组。我不知道如何，但是似乎 $ setUnion 已经自动对其进行了排序
I ended up add activity_type at each of my subdocument in array, and solve this using $setUnion to merge multiple array as an array, and use $slice to limit and skip the array. I don't know how, but it seems $setUnion already sorting it automatically
db.visitors.aggregate([
{
$project: {
activities: {
$setUnion: ['$activities.visits', '$activities.purchases'],
}
}
},
{
$project:{
activites: {
$slice: ["$activities", 0, 2]
}
}
}
])
推荐答案
如果将 activity_type 字段添加到每个数组元素中，则容易使用 $ setUnion 和一个 aggregation 阶段：
If you add activity_type field to each of the array elements, it will be easy to use $setUnion with one aggregation stage:
db.visitors.aggregate([
{
$project: {
activities: {
$setUnion: ['$activities.visits', '$activities.purchases']
}
}
}
])
在这种情况下，您将获得连接的数组
In this case you will get the arrays "concatenated" together into one activities array with their type.
对于问题的另一部分， sort ，跳过（限制）是聚合阶段，将对每个文档而不是子文档起作用，因此您需要 $ match 您所需的文档， $ unwind 结果活动数组从以前开始，然后您就可以使用所有 sort ， skip ， limit 运算符。
For the other part of your question, sort, skip, limit, are aggregation stages that will work on each document and not sub-documents, so you will need to $match your needed document, $unwind the result activities array from before, and then you can can use all of the sort, skip, limit operators.
这篇关于在聚合投影MongoDB中将多个子文档合并为数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
