CODEWITHSUNDEEP
mongodbmongodb-query
Cuzzo
Cuzzo

Reputation: 13

MongoDB sort by value in embedded document array

I have a MongoDB collection of documents formatted as shown below:

{
    "_id" : ...,
    "username" : "foo",
    "challengeDetails" : [ 
        {
            "ID" : ...,
            "pb" : 30081,
        }, 
        {
            "ID" : ...,
            "pb" : 23995,
        },
        ...
    ]
}

How can I write a find query for records that have a challengeDetails documents with a matching ID and sort them by the corresponding PB?

I have tried (this is using the NodeJS driver, which is why the projection syntax is weird)

const result = await collection
  .find(
    { "challengeDetails.ID": challengeObjectID},
    {
      projection: {"challengeDetails.$": 1},
      sort: {"challengeDetails.0.pb": 1}
    }
  )

This returns the correct records (documents with challengeDetails for only the matching ID) but they're not sorted.

I think this doesn't work because as the docs say:

When the find() method includes a sort(), the find() method applies the sort() to order the matching documents before it applies the positional $ projection operator.

But they don't explain how to sort after projecting. How would I write a query to do this? (I have a feeling aggregation may be required but am not familiar enough with MongoDB to write that myself)

Upvotes: 1

Views: 556

Answers (1)

varman
varman

Reputation: 8894

You need to use aggregation to sort n array

  • $unwind to deconstruct the array
  • $match to match the value
  • $sort for sorting
  • $group to reconstruct the array

Here is the code

db.collection.aggregate([
  { "$unwind": "$challengeDetails" },
  { "$match": { "challengeDetails.ID": 2 } },
  { "$sort": { "challengeDetails.pb": 1 } },
  {
    "$group": {
      "_id": "$_id",
      "username": { "$first": "$username" },
      "challengeDetails": { $push: "$challengeDetails" }
    }
  }
])

Working Mongo playground

Upvotes: 0

Related Questions