在聚合投影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屋!