Hello,I have 3 collections how to make a join to select the data with a single request?-CodePudding

I have three collections in mongodb i want to join them to get some datas. this is my Schema:

//user collection
{
    "_id": "616b429e0de99f6f74f911b9",
    "name": {
        "firstname": "Kisko",
        "lastName": "Barrisson"
    }
}
//Devices collection
{
    _id:"616b52453dcacbf8a3f989a8",
    user:"616b429e0de99f6f74f911b9",
    name:"driver",
    data:[{X:123,Y:200},{X:124,Y:300}]
}

//locations collection
{
    "_id": {
        "$oid": "6193b7a7751212e5358b481a"
    },
    "X": 123,
    "Y":200,
    "adresse":"My current adresse"
}

I would like to do something like: "Select l.adresse,d.name,u.name from user u LEFT JOIN devices d ON u._id=d.user LEFT JOIN locations l ON l.X = d.X AND L.Y=d.Y WHERE u._id=616b429e0de99f6f74f911b9". How can i do that using mongoose. Help me please!

CodePudding user response：

Query

start from devices, match the user
unwind data
(to make join easier, having the join fields inside arrays make things harder)
join with users(SQL like join)
join with locations

*you can avoid the replace roots, but they make the results of each stage looking simpler

*for performance make an index on devices.user, and on X,Y on the locations, also you need MongoDB 5, for index to be used in the second pipeline lookup

Test code here

devices.aggregate(
[{"$match": {"user": "616b429e0de99f6f74f911b9"}},
  {"$unwind": {"path": "$data"}},
  {"$lookup": 
    {"from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "joined__"}},
  {"$unwind": {"path": "$joined__"}},
  {"$replaceRoot": 
    {"newRoot": {"$mergeObjects": ["$joined__", "$$ROOT"]}}},
  {"$lookup": 
    {"from": "locations",
      "let": {"x": "$data.X", "y": "$data.Y"},
      "pipeline": 
      [{"$match": 
          {"$expr": 
            {"$and": [{"$eq": ["$$x", "$X"]}, {"$eq": ["$$y", "$Y"]}]}}}],
      "as": "joined"}},
  {"$unwind": {"path": "$joined"}},
  {"$replaceRoot": {"newRoot": {"$mergeObjects": ["$joined", "$$ROOT"]}}},
  {"$group": 
    {"_id": "$user",
      "data": 
      {"$push": 
        {"adresse": "$adresse", "X": "$X", "Y": "$Y", "name": "$data.name"}},
      "userName": {"$first": "$name"}}}])