How to query an array and retrieve it from MongoDB

Question

Updated:

I have a document on the database that looks like this:

My question is the following:

How can I retrieve the first 10 elements from the friendsArray from database and sort it descending or ascending based on the lastTimestamp value.

I don't want to download all values to my API and then sort them in Python because that is wasting my resources.

I have tried it using this code (Python):

listOfUsers = db.user_relations.find_one({'userId': '123'}, {'friendsArray' : {'$orderBy': {'lastTimestamp': 1}}}).limit(10)

but it just gives me this error pymongo.errors.OperationFailure: Unknown expression $orderBy

Any answer at this point would be really helpful! Thank You!

Answer 1

The translation of your find to aggregation(we need unwind that why aggregation is used) would be like the bellow query.

Test code here

Query (for descending replace 1 with -1)

db.collection.aggregate([
  {
    "$match": {
      "userId": "123"
    }
  },
  {
    "$unwind": {
      "path": "$friendsArray"
    }
  },
  {
    "$sort": {
      "friendsArray.lastTimeStamp": 1
    }
  },
  {
    "$limit": 10
  },
  {
    "$replaceRoot": {
      "newRoot": "$friendsArray"
    }
  }
])

If you want to skip some before limit add one stage also

{
  "$skip" : 10
}

To take the 10-20 messages for example.

Answer 2

use aggregate first unwind

then sort according timestap group by _id to create sorted array use addfields and filter for getting first 10 item of array

db.collection.aggregate([
  { $match:{userId:"123"}},
  {
    "$unwind": "$friendsArray"
  },
  {
    $sort: {
      "friendsArray.lastTimeStamp": 1
    }
  },
  {
    $group: {
      _id: "$_id",
      friendsArray: {
        $push: "$friendsArray"
      }
    },
    
  },
  {
    $addFields: {
      friendsArray: {
        $filter: {
          input: "$friendsArray",
          as: "z",
          cond: {
            $lt: [
              {
                $indexOfArray: [
                  "$friendsArray",
                  "$$z"
                ]
              },
              10
            ]
          }// 10 is n first item
          
        }
      }
    },
    
  }
])

https://mongoplayground.net/p/2Usk5sRY2L2

and for pagination use this

db.collection.aggregate([
  { $match:{userId:"123"}},
  {
    "$unwind": "$friendsArray"
  },
  {
    $sort: {
      "friendsArray.lastTimeStamp": 1
    }
  },
  {
    $group: {
      _id: "$_id",
      friendsArray: {
        $push: "$friendsArray"
      }
    },
    
  },
  {
    $addFields: {
      friendsArray: {
        $filter: {
          input: "$friendsArray",
          as: "z",
          cond: {
            $and: [
              {
                $gt: [
                  {
                    $indexOfArray: [
                      "$friendsArray",
                      "$$z"
                    ]
                  },
                  10
                ]
              },
              {
                $lt: [
                  {
                    $indexOfArray: [
                      "$friendsArray",
                      "$$z"
                    ]
                  },
                  20
                ]
              },
              
            ]
          }// 10 is n first item
          
        }
      }
    },
    
  }
])