CODEWITHSUNDEEP
mongodbmongoose
insivika
insivika

Reputation: 624

Sort query results based desc value of nested subdocument within array Mongoose/Mongodb

My documents look like this:

{
 { 
    mlsId: 'RTC749',
    firstName: 'Tommy',
    lastName: 'Davidson',
    officeMlsId: 'RTC2421',
    officeName: 'John Jones Real Estate LLC',
    slug: 'tommy-davidson',
    serviceAreas: [
    {
      name: 'Nashville',
      slug: 'nashville',
      type: 'city',
      totalClosedSales: 3
    },
    {
      name: 'Franklin',
      slug: 'franklin',
      type: 'city',
      totalClosedSales: 7
    }
  },
 {
    id: 'RTC7280',
    firstName: 'Jack',
    lastName: 'Miller',
    slug: 'jack-miller',
    serviceAreas: [
    {
      name: 'Nashville',
      slug: 'nashville',
      type: 'city',
      totalClosedSales: 4
    },
    {
      name: 'Franklin',
      slug: 'franklin',
      type: 'city',
      totalClosedSales: 10
    }
  ]
 },
}

The query to find documents based on slugs within the subdocuments looks like this:

    const localAgents = await Agent.find(
      {
        'serviceAreas.slug': locationSlug,
      },
      '-_id -__v'
    )
      .sort({ 'serviceAreas.totalClosedSales': -1 })

Note that I'd like to find agents by location slug and sort the result using totalClosedSales however I'm unable to get it to work. So the desired result would look like this:

{
  {
    id: 'RTC7280',
    firstName: 'Jack',
    lastName: 'Miller',
    slug: 'jack-miller',
    serviceAreas: [

    {
      name: 'Franklin',
      slug: 'franklin',
      type: 'city',
      totalClosedSales: 10
    },
    {
      name: 'Nashville',
      slug: 'nashville',
      type: 'city',
      totalClosedSales: 4
    }

]
  },

 { 
    mlsId: 'RTC749',
    firstName: 'Tommy',
    lastName: 'Davidson',
    officeMlsId: 'RTC2421',
    officeName: 'John Jones Real Estate LLC',
    slug: 'tommy-davidson',
    serviceAreas: [
    {
      name: 'Nashville',
      slug: 'nashville',
      type: 'city',
      totalClosedSales: 3
    },
    {
      name: 'Franklin',
      slug: 'franklin',
      type: 'city',
      totalClosedSales: 7
    }
    ]
  },
}

Upvotes: 3

Views: 1099

Answers (1)

varman
varman

Reputation: 8894

We can't sort array directly, But aggregation helps it

  • $unwind helps to de-structure the array
  • $sort helps to sort as you wish
  • $group helps to re-group the de-structured array

Mongo script is given below

db.collection.aggregate([
  {
    "$match": {
      "serviceAreas.slug": "nashville"
    }
  },
  {
    $unwind: "$serviceAreas"
  },
  {
    $sort: {
      "serviceAreas.totalClosedSales": -1
    }
  },
  {
    $addFields: {
      total: "$serviceAreas.totalClosedSales"
    }
  },
  {
    $sort: {
      total: -1
    }
  },
  {
    $group: {
      _id: "$_id",
      mlsId: {
        $first: "$mlsId"
      },
      firstName: {
        $first: "$firstName"
      },
      lastName: {
        $first: "$lastName"
      },
      slug: {
        $first: "$slug"
      },
      serviceAreas: {
        $push: "$serviceAreas"
      }
    }
  }
])

Working Mongo playground

Upvotes: 3

Related Questions