聚合 3 个 MongoDB 集合 -(Python、Flask、Jinja) [英] Aggregating 3 MongoDB collections - (Python, Flask, Jinja)
问题描述
多亏了这里的大力帮助，我设法为 MongoDB 中的 2 个集合进行了一些聚合.
@app.route("/perfume/", methods=["POST", "GET"])定义香水(ID):香水 = mongo.db.perfumes.find_one({"_id": ObjectId(id)})表单 = AddReviewForm()Cur = mongo.db.perfumes.aggregate([{$查找":{"from": "用户","localField": "作者","foreignField": "用户名","as": "创造者",}},{"$unwind": "$creator"},{$项目":{"_id": "$_id","perfumeName": "$name","perfumeBrand": "$brand","perfumeDescription": "$description","date_updated": "$date_updated","perfumePicture": "$图片","isPublic": "$public","perfumeType": "$perfume_type","username": "$creator.username","firstName": "$creator.first_name","lastName": "$creator.last_name","profilePicture": "$creator.avatar",}},{"$match": {"_id": ObjectId(id)}},])返回渲染模板("perfume.html", title="香水", 光标=cur, 香水=香水, 形式=形式) 我现在想向该聚合中添加第三个集合.
我的目标如下:我现在有 3 个集合，perfumes、users 和 reviews，我想在模板中显示香水(香水集合中的文档)、创建者的数据(用户集合中的文档)和评论中的数据(评论集合中的文档).
我目前正在设法完成前两个，但我现在正在添加评论香水"功能.
由于评论者也是用户，我想将这个新集合聚合到当前查找中.
这个集合(评论)只有 3 个字段:_id、review_author 和 review.review_author 必须出现在用户集合中，因为他们必须登录才能查看.
我还设法将评论文档的 _id(来自 reviews 集合)作为 perfumes 集合中的数组元素插入.为了在所有三个集合中包含模板数据，我需要的新聚合 lookup 怎么样?我很乐意阅读帮助！
目前我的架构如下:
<代码>{香水":{"_id": "_id","author": "<string>", <== 用于展开以下集合"brand": "","name": "","perfume_type": "","description": "<文本字段>","date_updated": "<日期>","public": "<布尔值>","图片": "<字符串>","review": "<数组>"},用户":{"_id": "_id","username": "<string>", <== 用于展开到上一个 coll，需要链接到下一个"first_name": "","last_name": "","email": "","is_admin": "<布尔值>","avatar": ""},评论":{"_id": "_id","review_author": "", <== 用作链接"review": "<文本字段>"},} 谢谢！
更新:
这是来自 perfumes
这是描述
",日期更新":{"$date": "2020-05-14T16:43:10.801Z"},公开":假，"图片": "generic.png",审查": [{"$oid": "5ebd752c0a52cd0dade39492"}]}]这是来自 users 的示例文档:
这是来自 reviews 的示例文档:
因此一个用户可以制作多种香水.一款香水可以有很多评论.不同的用户可以为相同或不同的香水创建评论.
更新 2:
这看起来非常有前途，我对此感到非常兴奋！我将在模板中简要说明我的目标:
{% for item in cursor &}<香水名><香水品牌><香水说明><香水图片><作者用户名(香水的)><作者姓名><创建日期><最终其他信息>{% if current_user == author %}<按钮编辑><按钮删除>{％ 万一 ％}<按钮查看 <=+ 可供所有登录用户使用>{% 结束为 %}{% for item in cursor['review'] <= 这不起作用 %}<香水评论><香水评论作者><香水评论作者头像><最终其他信息>{% 结束为 %}通过双重聚合，我实现了所有这些，除了显然是循环查看评论.展望未来，我相信这会有所进展！！:)P.S.:这只是为了让这个想法变得清晰！
试试下面的代码:
cur = db.perfumes.aggregate([{'$查找':{'来自':'用户'，'localField': '作者','foreignField': '用户名',作为":创造者"}},{'$unwind': '$creator'},{'$放松':{'path': '$review','preserveNullAndEmptyArrays': 真}},{'$查找':{'来自':'评论'，'让':{'rid':{'$toString':'$review._id'}}，'管道':[{'$匹配':{'$expr':{'$eq': ['$$rid', {'$toString': '$_id'}]}}}],'as': '用户评论'}},{'$unwind': '$userReviews'},{'$查找':{'来自':'用户'，'localField': 'userReviews.review_author','foreignField': '用户名','as': 'reviewUserInfo'}},{'$unwind': '$reviewUserInfo'},{'$组':{'_id':'$_id','香水名':{'$first':'$name'}，'香水品牌':{'$first':'$brand'}，'香水描述':{'$first':'$description'}，'date_updated': {'$first': '$date_updated'},'香水图片':{'$first':'$图片'}，'isPublic': {'$first': '$public'},'香水类型':{'$first':'$perfume_type'}，'用户名': {'$first': '$creator.username'},'firstName': {'$first': '$creator.first_name'},'lastName': {'$first': '$creator.last_name'},'profilePicture': {'$first': '$creator.avatar'},'用户评论':{'$推':{'reviewId': '$userReviews._id','review_author': '$userReviews.review_author','review': '$userReviews.review','review_author_first_name': '$reviewUserInfo.first_name','review_author_last_name': '$reviewUserInfo.last_name','review_author_email': '$reviewUserInfo.email','review_author_avatar': '$reviewUserInfo.avatar'}}}},])输出:
<代码>{"_id" : ObjectId("5ebd7eec93256dc00c8f6f6c"),"perfumeName": "一种新香水","perfumeBrand" : "一个新品牌","perfumeDescription" : "<p>这是描述</p>",日期更新":{$date":2020-05-14T16:43:10.801Z"},"perfumePicture": "generic.png",isPublic":假，"perfumeType": "东方","username" : "Guillermo","firstName" : "吉列尔莫","lastName": "布拉切塔",个人资料图片":空，用户评论":[{"reviewId" : ObjectId("5ebd752c0a52cd0dade39492"),"review_author": "吉列尔莫","review" : "<p>这是一篇评论</p>","review_author_first_name": "Guillermo","review_author_last_name": "Brachetta","review_author_email" : "brachetta@me.com","review_author_avatar" : "a92de23ae01cdfde.jpg"}]}PS - 您可以使用 $match 运算符来匹配特定用户，就像您在 OP 中所做的那样，您可以使用 $project运算符来投影更多字段并获得所需的输出.
Thanks to great help found here I managed to put in place some aggregation for 2 collections in MongoDB.
@app.route("/perfume/<id>", methods=["POST", "GET"])
def perfume(id):
perfume = mongo.db.perfumes.find_one({"_id": ObjectId(id)})
form = AddReviewForm()
cur = mongo.db.perfumes.aggregate(
[
{
"$lookup": {
"from": "users",
"localField": "author",
"foreignField": "username",
"as": "creator",
}
},
{"$unwind": "$creator"},
{
"$project": {
"_id": "$_id",
"perfumeName": "$name",
"perfumeBrand": "$brand",
"perfumeDescription": "$description",
"date_updated": "$date_updated",
"perfumePicture": "$picture",
"isPublic": "$public",
"perfumeType": "$perfume_type",
"username": "$creator.username",
"firstName": "$creator.first_name",
"lastName": "$creator.last_name",
"profilePicture": "$creator.avatar",
}
},
{"$match": {"_id": ObjectId(id)}},
]
)
return render_template(
"perfume.html", title="Perfumes", cursor=cur, perfume=perfume, form=form
)
I now would like to add a third collection to that aggregate.
My goal is the following: I have now 3 collections, perfumes, users and reviews and I want to display in a template a perfume (a document from the perfumes collection), data from the creator (which is a document in the users collection) and data from the review (a document from the reviews collection).
I'm currently managing to do the first two, but I'm adding the 'review perfume' functionality now.
Since reviewers are also users, I would like to aggregate this new collection to the current lookup.
This collection (reviews) has only 3 fields: _id, review_author and review. The review_author will necessarily be present in the users collection, since they have to be logged in to review.
I also managed to insert the _id of the review document (from the reviews collection) as an array element in the perfumes collection.
How's the new aggregate lookup I need in order to have in the template data from all three collections?
I'd be so happy to read help!
Currently my schemas are as follow:
{
"perfumes": {
"_id": "_id",
"author": "<string>", <== used to unwind with following collection
"brand": "<string>",
"name": "<string>",
"perfume_type": "<string>",
"description": "<text field>",
"date_updated": "<date>",
"public": "<boolean>",
"picture": "<string>",
"review": "<array>"
},
"users": {
"_id": "_id",
"username": "<string>", <== used to unwind to previous coll, needs to link to next
"first_name": "<string>",
"last_name": "<string>",
"email": "<string>",
"is_admin": "<boolean>",
"avatar": "<string>"
},
"reviews": {
"_id": "_id",
"review_author": "<string>", <== to be used as a link
"review": "<text field>"
},
}
Thank you!
UPDATE:
This is a sample document from perfumes
[
{
"_id": {
"$oid": "5ebd751e0a52cd0dade39491"
},
"author": "Guillermo",
"brand": "A new Brand",
"name": "A new Perfume",
"perfume_type": "Oriental",
"description": "<p>This is the description</p>",
"date_updated": {
"$date": "2020-05-14T16:43:10.801Z"
},
"public": false,
"picture": "generic.png",
"review": [
{
"$oid": "5ebd752c0a52cd0dade39492"
}
]
}
]
This is a sample document from users:
[
{
"_id": {
"$oid": "5eb09b0a7b62cdd2e8800d96"
},
"username": "Guillermo",
"first_name": "Guillermo",
"last_name": "Brachetta",
"email": "brachetta@me.com",
"password": "pbkdf2:sha256:150000$4vanQ3Qe$83031f081327e26c1125b861054f1d184bdb4373af323e6a10602587f6d40607",
"is_admin": true,
"avatar": "a92de23ae01cdfde.jpg"
}
]
And this is a sample document from reviews:
[
{
"_id": {
"$oid": "5ebd752c0a52cd0dade39492"
},
"review_author": "Guillermo",
"review": "<p>This is one review</p>"
}
]
So one user can create many perfumes. A perfume can have many reviews. Different users can create reviews for the same or different perfumes.
UPDATE 2:
This is looking very promising and I'm super excited about it! I'll make a short schema of what I'm aiming to do in the template:
{% for item in cursor &}
<perfume name>
<perfume brand>
<perfume description>
<perfume picture>
<author username (of the perfume)>
<author first and last names>
<date created>
<eventually other information>
{% if current_user == author %}
<button edit>
<button delete>
{% endif %}
<button review <=+ available to all logged in users>
{% endfor %}
{% for item in cursor['review'] <= this is not working %}
<perfume review>
<perfume review author>
<perfume review author avatar>
<eventually other information>
{% endfor %}
With the double aggregation I was achieving all of it except obviously the looping over the reviews. Looking forward, I'm sure this is getting somewhere!! :) P.S.: This is just very schematic to try to be clear with the idea!
Try the below code:
cur = db.perfumes.aggregate([
{
'$lookup': {
'from': 'users',
'localField': 'author',
'foreignField': 'username',
'as': 'creator'
}
},
{
'$unwind': '$creator'
},
{
'$unwind': {
'path': '$review',
'preserveNullAndEmptyArrays': True
}
},
{
'$lookup': {
'from': 'review',
'let': {'rid': {'$toString': '$review._id'}},
'pipeline': [{
'$match': {
'$expr': {
'$eq': ['$$rid', {'$toString': '$_id'}]
}
}
}],
'as': 'userReviews'
}
},
{
'$unwind': '$userReviews'
},
{
'$lookup': {
'from': 'users',
'localField': 'userReviews.review_author',
'foreignField': 'username',
'as': 'reviewUserInfo'
}
},
{
'$unwind': '$reviewUserInfo'
},
{
'$group': {
'_id':'$_id',
'perfumeName': {'$first': '$name'},
'perfumeBrand': {'$first': '$brand'},
'perfumeDescription': {'$first': '$description'},
'date_updated': {'$first': '$date_updated'},
'perfumePicture': {'$first': '$picture'},
'isPublic': {'$first': '$public'},
'perfumeType': {'$first': '$perfume_type'},
'username': {'$first': '$creator.username'},
'firstName': {'$first': '$creator.first_name'},
'lastName': {'$first': '$creator.last_name'},
'profilePicture': {'$first': '$creator.avatar'},
'userReviews': {
'$push': {
'reviewId': '$userReviews._id',
'review_author': '$userReviews.review_author',
'review': '$userReviews.review',
'review_author_first_name': '$reviewUserInfo.first_name',
'review_author_last_name': '$reviewUserInfo.last_name',
'review_author_email': '$reviewUserInfo.email',
'review_author_avatar': '$reviewUserInfo.avatar'
}
}
}
},
])
Output:
{
"_id" : ObjectId("5ebd7eec93256dc00c8f6f6c"),
"perfumeName" : "A new Perfume",
"perfumeBrand" : "A new Brand",
"perfumeDescription" : "<p>This is the description</p>",
"date_updated" : {
"$date" : "2020-05-14T16:43:10.801Z"
},
"perfumePicture" : "generic.png",
"isPublic" : false,
"perfumeType" : "Oriental",
"username" : "Guillermo",
"firstName" : "Guillermo",
"lastName" : "Brachetta",
"profilePicture" : null,
"userReviews" : [
{
"reviewId" : ObjectId("5ebd752c0a52cd0dade39492"),
"review_author" : "Guillermo",
"review" : "<p>This is one review</p>",
"review_author_first_name" : "Guillermo",
"review_author_last_name" : "Brachetta",
"review_author_email" : "brachetta@me.com",
"review_author_avatar" : "a92de23ae01cdfde.jpg"
}
]
}
P.S. - You can use $match operator to match a particular user, as you have done in OP and you can use $project operator to project more fields and get the required output.
这篇关于聚合 3 个 MongoDB 集合 -(Python、Flask、Jinja)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
