Javascript - MongoDB complex aggregate query depends on an Ids array order?

KienPham
August 2, 2023
162 views
1 vote
2 Answers

I have a question for complex query which I tried many ways but result is not as expected. I have User model with likedVideos field (ref: 'Videos' model). When user like a video, it will push _id to this likedVideos array. Latest item will be the one with biggest index number.

User model:

{
  "_id": ObjectId("user_id"),
  "likedVideos": [
    ObjectId("video_1"), // oldest
    ObjectId("video_2"),
    ObjectId("video_3"), // latest
  ],
}

Now I have to render liked video to Frontend with ability to add skip and limit for lazyload. My code below, my query URL will be api/video/liked?userId=xxx&skip=0&limit=10

const { userId, skip, limit } = req.query;
  const pipeline = [
    {
      $match: {
        _id: ObjectId(userId),
      },
    },
    {
      $lookup: {
        from: "videos",
        localField: "likedVideos",
        foreignField: "_id",
        as: "likedList",
      },
    },
    {
      $unwind: "$likedList",
    },
    {
      $group: {
        _id: "$_id",
        likedList: { $push: "$likedList" },
      },
    },
    {
      $project: {
        likedList: {
          $slice: ["$likedList", parseInt(skip), parseInt(limit)],
        },
      },
    },
  ];

  const likedVideos = await User.aggregate(pipeline);
  return res.status(200).send(likedVideos);

The result is not shorted as I want, latest liked video should shows first and I’m not able to get correct data when changing skip, limit.

Result I get: (result not show correctly if I pass skip/limit and shorting not correctly as well)

[{
  _id: ObjectId("video_1"),
  videoUrl: "xxx"
},
{
  _id: ObjectId("video_3"),
  videoUrl: "yyy"
},
{
  _id: ObjectId("video_2"),
  videoUrl: "zzz"
},
]

Result I want: (correct order, correct value if I pass skip/limit)

[{
  _id: ObjectId("video_3"),
  videoUrl: "yyy"
},
{
  _id: ObjectId("video_2"),
  videoUrl: "zzz"
},
{
  _id: ObjectId("video_1"),
  videoUrl: "xxx"
}]

Thank you for your help

Answers

Chosen as BEST ANSWER

I found the answer, can you check if this code is good or bad. I'm not sure but the result is correct.

const pipeline = [
        {
          $match: {
            _id: ObjectId(userId),
          },
        },
        {
          $lookup: {
            from: "videos",
            localField: "liked_videos",
            foreignField: "_id",
            as: "likedVideos",
          },
        },
        {
          $unwind: {
            path: "$likedVideos",
            preserveNullAndEmptyArrays: true,
          },
        },
        {
          $addFields: {
            likedIndex: {
              $indexOfArray: ["$liked_videos", "$likedVideos._id"],
            },
          },
        },
        {
          $sort: {
            likedIndex: -1,
          },
        },
        {
          $skip: parseInt(skip),
        },
        {
          $limit: parseInt(limit),
        },
        {
          $replaceRoot: {
            newRoot: "$likedVideos",
          },
        },
      ];

(Edit)

I think your approach is lacking a $sort stage, which will cause the videos to appear in an undefined order. Applying skip/limit could be done differently. Also removing the $group stage will maintain the correct structure of the documents after unwinding, ensuring that the sorting and pagination work as intended. Give something like this a try

const { userId, skip, limit } = req.query;
const pipeline = [
  {
    $match: {
      _id: ObjectId(userId),
    },
  },
  {
    $lookup: {
      from: "videos",
      localField: "likedVideos",
      foreignField: "_id",
      as: "likedList",
    },
  },
  {
    $unwind: "$likedList",
  },
  {
    $sort: {
      "likedList._id": -1, // Sort in descending order based on index
    },
  },
  {
    $skip: parseInt(skip),
  },
  {
    $limit: parseInt(limit), 
  },
    $project: {
      _id: "$likedList._id", // Include _id from the likedList subdocument
      videoUrl: "$likedList.videoUrl", // Include videoUrl from the likedList subdocument
    },
];

const likedVideos = await User.aggregate(pipeline);
return res.status(200).send(likedVideos);

Please signup or login to give your own answer.

Click here to cancel reply.

Javascript – MongoDB complex aggregate query depends on an Ids array order?

Answers