I am trying to get a list of users with total transaction count and each user should have latest transaction detail as embedded object using MongoDB's aggregate pipelines to fetch results in GET API.
I have the following database schema:
User: _id, name, phone, address
Product: _id, name, unit_price, description
Transaction: _id, date, product_id(ref to Product), user_id(ref to User), quantity, total_price
Expected Response JSON
[
{
name:"",
phone:"",
address:"",
total_transaction:
latest_transaction_detail: {
product_id:
quantity:
total_price:
}
},
{
name:"",
phone:"",
address:"",
total_transaction:
latest_transaction_detail: {
product_id:
quantity:
total_price:
}
}
]
How do I generate an aggregate query to return the above?
You can achieve this by running an aggregation query.
A lookup stage will join your User collection with your Transaction collection (no need to join Product in your expected result). Its pipeline is splitted with $facet, for both get the count result and the latest transaction for that user
A project stage will reshape your data and extract array elements to documents.
Here's such a query :
db.User.aggregate(
[
{
$lookup:
{
from: "Transaction",
let: { userId: "$_id" },
pipeline: [
{$facet:
{count:[{$match:{$expr:{$eq:["$$userId","$user_id"]}}}, {$count:"total_transaction"}],
latest:[
{$match:{$expr:{$eq:["$$userId","$user_id"]}}},
{$sort:{date:-1}},
{$limit:1}]
} }],
as: "transactions"
}
},
{
$project: {
last_name:1,
phone:1,
address:1,
total_transaction : {
$let:{
vars:{
count:{
$arrayElemAt:["$transactions.count",0]
}
},
in:{
$arrayElemAt:["$$count.total_transaction",0]
}
}
},
latest_transaction : {
$let:{
vars:{
latest:{
$arrayElemAt:["$transactions.latest",0]
}
},
in:{
$arrayElemAt:["$$latest",0]
}
}
},
}
},
]
);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments