MongoDB multiple/nested aggregations

Question

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

Takis · Accepted Answer · 2022-04-09 17:43:21Z

1

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

PlayMongo

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"
    }
  }
])

edited Apr 9, 2022 at 17:43

answered Oct 14, 2021 at 0:33

Takis

8,7552 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Takis Over a year ago

i updated the answer i think this is now what you wanted

Takis Over a year ago

ok i think you want for each track all track information, i updated to do 2 more lookups after you finish, to get the extra info for those 5 + 5 tracks. We dont have the data for tracks 15/20 etc so its empty, but it will work if we had the complete data

Takis Over a year ago

in the data that i use i dont have track 2,track 3 etc,you gave data for 1 track only, in last update i remove the track from the creator-tracks (i added one more filter). Now creator tracks have all except the trackID(where we started), and category has all except the creators tracks

Takis Over a year ago

you can do the 2 last lookup and at results put their names they will be replaced, instead of creator-tracks-info put creator-tracks, and instead of related-tracks-info put related-tracks, they will be replaced. You could also $unset them, no need to send those data to the client. For example this

daniel93 Over a year ago

exactly as I expected, you saved me a lot of frustration :) appreciate your time & help!

|

Collectives™ on Stack Overflow

MongoDB multiple/nested aggregations

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related