Sorting with mongoose and mongoDB

[Imad_Bouteraa]

@muhammed_ogunsanya

i was prohibited from sending out any more replies yesterday.

lets say you have:

{firstName:"a", email:"z"}
{firstName:"b", email:"a"}

are you willing to get ?

{firstName:"a", email:"a"}
{firstName:"b", email:"z"}

otherwise, provide the

@Stennie_X

expected output for your four sample documents.

[muhammed_ogunsanya]

Yes! that would be great.! also if i ad order sorting fields it should maintain this behavior.

@Imad_Bouteraa

{firstName:"a", email:"a"}
{firstName:"b", email:"z"}

[Imad_Bouteraa]

try this, I hope that you don’t run into memory issues;

db.collection.aggregate([
  {
    $facet: {
      firstNames: [
        {
          "$sort": {
            "firstName": 1
          }
        },
        {
          "$project": {
            "_id": 0,
            firstName: 1
          }
        }
      ],
      emails: [
        {
          "$sort": {
            "email": 1
          }
        },
        {
          "$project": {
            "_id": 0,
            email: 1
          }
        }
      ]
    }
  },
  {
    $unwind: {
      path: "$firstNames",
      includeArrayIndex: "firstNames_index",
      
    }
  },
  {
    $unwind: {
      path: "$emails",
      includeArrayIndex: "emails_index",
      
    }
  },
  {
    $project: {
      firstNames: 1,
      emails: 1,
      compare: {
        $cmp: [
          "$firstNames_index",
          "$emails_index"
        ]
      }
    }
  },
  {
    $match: {
      compare: 0
    }
  },
  {
    $project: {
      _id: 0,
      firstName: "$firstNames.firstName",
      email: "$emails.email"
    }
  }
])

[muhammed_ogunsanya]

This is exactly what i’ve been looking for! Thank you so much! However i try to add for fields for accommodate this context for email, firstName, lastName, createdAt etc but the it said the $cmp argument can only take 2 fields… how do i approach this? Thank you once again! @Imad_Bouteraa

[muhammed_ogunsanya]

Also about the memory issue… i’ll present an option to my team mates. i have another method which is to query them singularly. but some of my team mates want us to make all queries in a stretch. Thank you!

[Stennie_X (Stennie)]

@muhammed_ogunsanya

is there a way to sort by multiple fields in the same query? in this case, if i get the lastName in asc order, can i also get the email in asc order simultaneously.

Hi @muhammed_ogunsanya,

A compound sort considers all of the fields in the sort specification, but it sounds like what you are actually looking for is the same result set with two different sort orders.

You could produce this in a single aggregation query using a $facet stage per @Imad_Bouteraa’s example, however the most performant (and my strongly recommended) approach would be to execute multiple independent queries so each can Use Indexes to Sort Query Results.

There’s currently an important caveat on $facet Index Use as noted in the documentation:

The $facet stage, and its sub-pipelines, cannot make use of indexes, even if its sub-pipelines use $match or if $facet is the first stage in the pipeline. The $facet stage will always perform a COLLSCAN during execution.

A collection scan with two in-memory sorts for every aggregation query will definitely become problematic at scale.

Regards,
Stennie

[muhammed_ogunsanya]

Thank you guys! i’ve discussed with my team mates and we’ve opted for another way to achieve our am.