I have these collections:

users

{
  _id: "userId1",
  // ...
  tracks: ["trackId1", "trackId2"],
};

tracks

{
  _id: "trackId1",
  // ...
  creatorId: "userId1",
  categoryId: "categoryId1"
}

categories

{
  _id: "categoryId1",
  // ...
  tracks: ["trackId1", "trackId15", "trackId20"],
};

by using the following code, I am able to get a track by its ID and add the creator

tracks.aggregate([
        {
          $match: { _id: ObjectId(trackId) },
        },
        {
          $lookup: {
            let: { userId: { $toObjectId: "$creatorId" } },
            from: "users",
            pipeline: [{ $match: { $expr: { $eq: ["$_id", "$$userId"] } } }],
            as: "creator",
          },
        },
        { $limit: 1 },
      ])
      .toArray();

Response:

"track": {
    "_id": "trackId1",
    // ...
    "categoryId": "categoryId1",
    "creatorId": "userId1",
    "creator": {
        "_id": "userId1",
        // ...
        "tracks": [
            "trackId5",
            "trackId10",
            "trackId65"
        ]
    }
}

but what I am struggling with is that I want the creator.tracks to aggregate also returning the tracks by their ID (e.g up to last 5), and also to get the last 5 tracks from the categoryId

expected result:

"track": {
    "_id": "trackId1",
    // ...
    "categoryId": "categoryId1",
    "creatorId": "userId1",
    "creator": {
        "_id": "userId1",
        "tracks": [
            {
                "_id": "trackId5",
                // the rest object without the creator
            },
            {
                "_id": "trackId10",
                // the rest object without the creator
            },
            {
                "_id": "trackId65",
                // the rest object without the creator
            },
        ]
    },
    // without trackId1 which is the one that is being viewed
    "relatedTracks": [
        {
            "_id": "trackId15",
            // the rest object without the creator
        },
        {
            "_id": "trackId20",
            // the rest object without the creator
        },
    ]
}

I would appreciate any explanation/help to understand what is the best one to do it and still keep the good performance

CodePudding user response：

Query

• start from a track
• join with users using the trackId get all the tracks of the creator (creator-tracks)
• join with categories using the categoryId to get all the tracks of the category (related tracks)
• remove from related-tracks the tracks of the creator
• take the last 5 from both using $slice (creator-tracks and related-tracks)

*i added 2 extra lookups to get all info of the tracks, its empty arrays because i dont have enough data(i have only trackId1), with all the data it will work

Test code here

db.tracks.aggregate([
  {
    "$match": {
      "_id": "trackId1"
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "creatorId",
      "foreignField": "_id",
      "as": "creator-tracks"
    }
  },
  {
    "$set": {
      "creator-tracks": {
        "$arrayElemAt": [
          "$creator-tracks.tracks",
          0
        ]
      }
    }
  },
  {
    "$lookup": {
      "from": "categories",
      "localField": "categoryId",
      "foreignField": "_id",
      "as": "related-tracks"
    }
  },
  {
    "$set": {
      "related-tracks": {
        "$arrayElemAt": [
          "$related-tracks.tracks",
          0
        ]
      }
    }
  },
  {
    "$set": {
      "related-tracks": {
        "$filter": {
          "input": "$related-tracks",
          "cond": {
            "$not": [
              {
                "$in": [
                  "$$this",
                  "$creator-tracks"
                ]
              }
            ]
          }
        }
      }
    }
  },
  {
    "$set": {
      "creator-tracks": {
        "$slice": [
          {
            "$filter": {
              "input": "$creator-tracks",
              "cond": {
                "$ne": [
                  "$$this",
                  "$_id"
                ]
              }
            }
          },
          -5
        ]
      }
    }
  },
  {
    "$set": {
      "related-tracks": {
        "$slice": [
          "$related-tracks",
          -5
        ]
      }
    }
  },
  {
    "$lookup": {
      "from": "tracks",
      "localField": "creator-tracks",
      "foreignField": "_id",
      "as": "creator-tracks-all-info"
    }
  },
  {
    "$lookup": {
      "from": "tracks",
      "localField": "related-tracks",
      "foreignField": "_id",
      "as": "related-tracks-all-info"
    }
  }
])