17 สิงหาคม 2563

MongoDB query join multiple collections using aggregate

MongoDB aggregate allows us to query and join multiple collections (like JOIN in SQL).


Sample Usage 

Let say, we have 3 collections like this


// customers collection
{
id: "c1",
name: "Robert Tee",
created: new Date()
}

// tickets collection
{
id: "t1",
seats: ["A1", "A2"],
customerId: "c1",
merchantId: "m1"
created: new Date()
}

// merchants collection
{
id: "m1",
name: "Merchant One"
created: new Date()
}


We would like to see the last ticket of the merchant "Merchant One" of the customer name "Robert Tee".

We can use the following MongoDB query command

// finding the last ticket of merchant ""Merchant One""
// of customer name "Robert Tee",
db.customers.aggregate([
// filter by customer name
{ $match: { "name": "Robert Tee" }},
// join tickets colletion
{
$lookup: {
from: "tickets",
localField: "id",
foreignField:
"customerId",
as: "ticket"
}
},
{ $unwind: {
path: "$ticket",
preserveNullAndEmptyArrays: false
}},
// join merchants colletion
{
$lookup: {
from: "merchants",
localField: "ticket.merchantId",
foreignField: "id",
as: "merchant"
}
},
{ $unwind: {
path: "$merchant",
preserveNullAndEmptyArrays: false
}},
// filter by merchant short name
{ $match: { "merchant.name": "Merchant One" }},
// order by created desc
{ $sort: { "ticket.created": -1}},
// get 1
{ $limit: 1 }
])




ไม่มีความคิดเห็น:

แสดงความคิดเห็น

บทความยอดนิยม (ล่าสุด)

บทความยอดนิยม (1 ปีย้อนหลัง)