好的，我是這個 mongodb 世界的新手，我遇到了一些麻煩。
我有 3 個集合、產品、評論 y 用戶。products 存盤來自 comments 的 objectid 陣列，comments 存盤 user_id。
這是產品檔案示例。
{
"_id": {
"$oid": "625f1825fd0569646907dbb2"
},
"id": {
"$numberInt": "1"
},
"title": "Fjallraven - Foldsack No. 1 Backpack, Fits 15 Laptops",
"price": {
"$numberDouble": "109.95"
},
"description": "Your perfect pack for everyday use and walks in the forest. Stash your laptop (up to 15 inches) in the padded sleeve, your everyday",
"category": "men's clothing",
"image": "https://fakestoreapi.com/img/81fPKd-2AYL._AC_SL1500_.jpg",
"rating": {
"rate": {
"$numberDouble": "3.9"
},
"count": {
"$numberInt": "120"
}
},
"comments": [
{
"id": {
"$oid": "62607a918aaf3e14a2cd1f0e"
}
}
]
}
這是評論示例
{
"_id": {
"$oid": "62607a918aaf3e14a2cd1f0e"
},
"user_id": {
"$oid": "625f1ae4b0e88cd486276a54"
},
"comment": "asdjfaslkdf",
"product_id": {
"$oid": "625f1825fd0569646907dbb2"
},
"rating": {
"rate": {
"$numberInt": "0"
},
"votes": {
"$numberInt": "0"
}
}
}
這是用戶示例。
{
"_id": {
"$oid": "625f1ae4b0e88cd486276a54"
},
"name": "Ursula",
"username": "lahee",
"create_at": {
"$date": {
"$numberLong": "1650399972095"
}
},
"password": "$2a$10$Av3HTLljhDkDxoVEyRKGwe4M42A4HHZMWcCfThdOQHbmA3H5EGsny",
"email": "[email protected]",
"phone": "102394523",
"preferences": [],
"token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYyNWYxYWU0YjBlODhjZDQ4NjI3NmE1NCIsInVzZXJuYW1lIjoibGFoZWUiLCJpYXQiOjE2NTA0ODMzMzEwODUsImV4cCI6MTY1MDQ4MzMzNDY4NX0.UXV7I-1XBZ3T_Y0aPBPdP-gdkPMqvM6dEnZcHXg5_5g",
"role": "user",
"__v": {
"$numberInt": "0"
}
}
有了這個檔案，我想得到所有帶有評論和用戶名的產品，以及這樣的電子郵件：
{
"_id": {
"$oid": "625f1825fd0569646907dbb2"
},
"price": 109.95,
"description": "Your perfect pack for everyday use and walks in the forest. Stash your laptop (up to 15 inches) in the padded sleeve, your everyday",
"comments": [
{
"_id": {
"$oid": "62607a918aaf3e14a2cd1f0e"
},
"comment": "asdjfaslkdf",
"user": [
{
"_id": {
"$oid": "625f1ae4b0e88cd486276a54"
},
"name": "Ursula",
"email": "[email protected]"
}
]
}
]
}
我嘗試了一些規則和查詢，最好給我所有評論的相同用戶。
db.products.aggregate([
{
$lookup: {
from: "comments",
let: { comment_id: "$comments.id" },
pipeline: [
{ $match: { $expr:{ $eq:[ "$_id", "$$comment_id" ] } } },
{
$lookup: {
from: "users",
localField: "user_id",
foreignField: "_id",
as: "user"
}
},
],
as: "comments"
}
},
{
$project:{
_id:1,
name:1,
price:1,
description:1,
comments:{
_id:1,
comment:1,
date_at:1,
rate:1,
user:{
_id:1,
name:1,
email:1
},
},
}
}
])
任何人都可以幫助我解決這個問題，我想更好地了解管道和 mongodb 查詢。
謝謝。
uj5u.com熱心網友回復：
這是獲得所需輸出的一種方法。
db.products.aggregate([
{ // may be multiple comments (or none!)
"$unwind": {
"path": "$comments",
"preserveNullAndEmptyArrays": true
}
},
{ // two-stage lookup
"$lookup": {
"from": "comments",
"localField": "comments.id",
"foreignField": "_id",
"pipeline": [
{ // get user info
"$lookup": {
"from": "users",
"localField": "user_id",
"foreignField": "_id",
"pipeline": [
{
"$project": {
"name": 1,
"email": 1
}
}
],
"as": "users"
}
},
{
"$project": {
"comment": 1,
"users": 1
}
}
],
"as": "comments"
}
},
{ // only fields needed
"$project": {
"comments": 1,
"description": 1,
"price": 1
}
},
{ // groups all comments together
"$group": {
"_id": "$_id",
"description": { "$first": "$description" },
"price": { "$first": "$price" },
"comments": { "$push": { "$first": "$comments" } }
}
}
])
在mongoplayground.net上試試。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/463097.html