Sorting a populated field

Working with Data
1

Hi, this is my first post here, I’m glad I found this community. I’ve been working with mongo for a couple of years and I implemented it in a new app, now in production. (using mongoose). (My question is basically about mongoose, if this is not the correct place, I apologise.)

I am now refactoring and trying to get the best of mongo. One problem I found was trying to sort a populated field, which is an array of objects. I need to sort inside the array by createdAt field.

const ShirtSchema = new Schema({
     {...}
    images: {
        type: [{
            type: Schema.Types.ObjectId,
            ref: 'Image'
        }]
    },
 {...}
},
);

I have the models Shirt and Image (yes, I now know that probably it would’ve been better to embed image into shirt), and Shirt has an array of Image objectId. Those images can be up to three, and it is very important for the app to have the images sorted asc by date.

I’ve seen options like this in stackoverflow

  .populate({path: 'spells', options: { sort: [['damages', 'asc']] }})

but didn’t work.

I wanted to ask what would be the best approach (if there is one). I tried to use aggregate but also didn’t get the result. Do you think that I should change the structure of the database, and embed image instead of making reference. I basically did it because I am using the model image also for other things, and I think is more clear, but now I am really asking myself if it was necessary.

Some advice would be really good for me, not only for this issue in particular, but also for the future.

Thank you very much.

2

Hi @Juan_Ignacio_Benito,

Welcome to MongoDB community!

It sounds like embedding might be a good option but you will still need to push the images in an ordered way.

Please note I am not that familiar with mongoose but MongoDB allows you to push in a sorted manner based on an array object field when using $each and $sort with $push.

If you query this data why can’t you sort it by createDate? Is that a $lookup or 2 seperate queries?

Can you provide sample docs from both collections and the current queries used?

Thanks
Pavel

3

Hi Pavel, thank you very much for your response.

Sounds like pushing the images in an ordered way might work. So it would be when saving the images, doing it using $each and $sort with $push?

I am now using lookup…this is the query…

  const shirts = await _shirt
            .aggregate([
                {
                    $lookup: {
                        from: _team.collection.name,
                        localField: 'team',
                        foreignField: '_id',
                        as: 'team',
                    },
                },
                {
                    $lookup: {
                        from: _comment.collection.name,
                        localField: 'comments',
                        foreignField: '_id',
                        as: 'comments',
                    },
                },
                {
                    $lookup: {
                        from: _image.collection.name,
                        localField: 'images',
                        foreignField: '_id',
                        as: 'images',
                    },
                },
                {
                    $addFields: { totalLikes: { "$size": "$likes" }, totalComments: { "$size": "$comments" } }
                },
                {
                    "$project": {
                        "_id": "$$ROOT", "images": "$images"
                    }
                },
                {
                    $unwind:
                    {
                        path: "$images",
                        preserveNullAndEmptyArrays: true
                    }
                },
                {
                    "$sort": { "images.imageName": 1 }
                },
                {
                    "$group": {
                        "_id": "$_id",
                        "_images": { "$push": "$images" }
                    }
                },
                {
                    "$project": {
                        "_id": "$_id._id",
                        "totalLikes": "$_id.totalLikes",
                        "totalComments": "$_id.totalComments",
                        "images": "$_images",
                        "team": "$_id.team",
                        "title": "$_id.title",
                        "comments": "$_id.comments",
                        "statusType": "$_id.statusType",
                        "size": "$_id.size",
                        "year": "$_id.year",
                        "brand": "$_id.brand",
                        "code": "$_id.code",
                        "description": "$_id.description",
                        "isHome": "$_id.isHome",
                        "isFan": "$_id.isFan",
                        "isNewShirt": "$_id.isNewShirt",
                        "shirtUser": "$_id.shirtUser",
                        "likes": "$_id.likes",
                        "isSoftDeleted": "$_id.isSoftDeleted",
                        "createdAt": "$_id.createdAt",
                        "updatedAt": "$_id.updatedAt"
                    }
                },
                {
                    $unwind:
                    {
                        path: "$team",
                        preserveNullAndEmptyArrays: true
                    }
                },
                {
                    $match: filters
                },
                {
                    $sort: sorted
                },
                {
                    $skip: skips
                },
                {
                    $limit: pageSize
                }
            ])
            .collation({ locale: "es" })
        return shirts
    };

So far, the array of images is being sorted in client side…

{
  "shirts": [
    {
      "_id": "5f7d36a151b64500193f3a6e",
      "totalLikes": 2,
      "totalComments": 2,
      "images": [
        {
          "_id": "5f7d36a151b64500193f3a6c",
          "cloudImage": "xxx.amazonaws.com/03181272-b320-4269-98c0-2ecbf5397597",
          "imageName": "image0",
          "createdAt": "2020-10-07T03:31:45.414Z",
          "updatedAt": "2020-10-07T03:31:45.414Z",
          "__v": 0
        },
        {
          "_id": "5f7d36a151b64500193f3a6d",
          "cloudImage": "xxx.amazonaws.com/d971ac08-fe97-45e1-bbff-37d83e939dc4",
          "imageName": "image1",
          "createdAt": "2020-10-07T03:31:45.414Z",
          "updatedAt": "2020-10-07T03:31:45.414Z",
          "__v": 0
        }
      ],
      "team": {
        "_id": "5f031e9bed4851001a8aceb6",
        "name": "Vasco Da Gama",
        "unique_id": 159,
        "country": "5f031e9aed4851001a8ace0d",
        "__v": 0,
        "createdAt": "2020-07-06T12:52:43.516Z",
        "updatedAt": "2020-07-06T12:52:43.516Z"
      },
      "title": "Vasco da Gama",
      "comments": [
        {
          "_id": "5f7e15370afb6d002063132e",
          "commentUser": {
            "image": {
              "_id": "5f01ffd3a5790ba8dcb2de16",
              "cloudImage": "linktoimage",
              "imageName": "userImage17"
            },
            "userId": "5eab70a2a368f33b2ba0d4e0",
            "username": "tomasm",
            "isVerified": false
          },
          "isSoftDeleted": false,
          "text": "Holaaa",
          "createdAt": "2020-10-07T19:21:27.730Z",
          "updatedAt": "2020-10-07T19:21:27.730Z",
          "__v": 0
        },
        {
          "_id": "5f7f85d19c46020020801e33",
          "commentUser": {
            "image": {
              "_id": "5f0212c0a5790b63e7b2de2a",
              "cloudImage": "linktoimage",
              "imageName": "userImage17"
            },
            "userId": "5eab7597a368f3eefea0d505",
            "username": "museo.g",
            "isVerified": false
          },
          "isSoftDeleted": false,
          "text": "Me da un miedo ver las casacas de los usuarios acá ja",
          "createdAt": "2020-10-08T21:34:09.287Z",
          "updatedAt": "2020-10-08T21:34:09.287Z",
          "__v": 0
        }
      ],
      "statusType": 0,
      "size": "P",
      "year": 1999,
      "brand": "Kappa",
      "code": "",
      "description": "Alternativa, manga larga, 99-00. Horrible la publicidad de ACE",
      "isHome": true,
      "isFan": true,
      "isNewShirt": false,
      "shirtUser": {
        "image": {
          "_id": "5f60ace02fec53001b444bb9",
          "cloudImage": "linktoimage",
          "imageName": "userImage17"
        },
        "deviceToken": "xxx",
        "userId": "5f237f550e1545b19c8cf94c",
        "username": "xxx",
        "isVerified": false
      },
      "likes": [
        "5eab70a2a368f33b2ba0d4e0",
        "5eab7597a368f3eefea0d505"
      ],
      "isSoftDeleted": false,
      "createdAt": "2020-10-07T03:31:45.421Z",
      "updatedAt": "2020-10-08T21:34:09.299Z"
    }
  ],
}

This sample doc has a “shirt” with 2 images. What I am seeing now is that they are created in the exact same time, so that could be the possible problem. Anyway, it would be great for me if you could advise on how to perform this operation the best possible way.

Thank you again!

4

Hi @Juan_Ignacio_Benito,

Your query has 3 $lookups which is by design an antipattern for MongoDB.

If data is queried together it needs to be stored together.

This is the main design problem in your schema

I would say that further using skip and limit for pagination is also a bad idea.

Read following blogs for better methods:

So it would be when saving the images, doing it using $each and $sort with $push?

It would help but very slightly. Please read more on antipattern and design recommendations here

https://www.mongodb.com/article/schema-design-anti-pattern-summary

Best
Pavel

5

Hi Pavel, thank you very much for your response. Is it being really helpful.
I’ve been analysing carefully my design I will start to make changes (as long as production database allow me).
I wanted to ask you what would be the best approach to update the current data. For example, now I have in my model “shirt” a reference to model “image”. I want/need to embed image into shirt. I have 1000 documents in shirt collection. Creating a script that loops over the collection would be a good idea?.
I wanted to ask you also if you have some program in which you can take a look at my code, and I can get some personal advise.
Thank you very much again.

6

Hi @Juan_Ignacio_Benito,

First we offer consulting packages to help you with schema tuning and migrations of your data. Please let me know if you are interested and I will make sure you be contacted.

Now regarding the way to migrate, as the amount of data sounds small but the changes are drastic probably you can do the migration as part of you code when you lunch the new version you will query and bulk replace all documents with new format and then your application release should be able to work with the migrated model.

Best
Pavel

7

Hi Pavel,
Thank you for your response!. Yes please, I am interested, if it is possible to get in touch with you regarding the consulting packages would be great.