MongoDB sort array before $slice

Question

I have an aggregation pipeline that returns Regions and their respective countries with Sales value. Now I want top 5 countries by sales in each region so I'm using $slice but it returns first 5 countries without any order. How do I sort the Countries array before slicing it ? Elements of Countries array look like this:

{name:"USA",sales:999}

The project query looks like this:

    "$project": {
       "Region":1,
       "TotalSales":1,
       "Countries": {
         "$slice": [
           "$Countries",
           5
         ]
       }
    }

MongoDB v5.0

@cmgchess sort countries by their sales..updated question with the info — sandeep.kgp
– sandeep.kgp, Commented Mar 25, 2023 at 19:12
can you check if below works. i also attached a playground link — cmgchess
– cmgchess, Commented Mar 25, 2023 at 19:14

cmgchess · Accepted Answer · 2023-03-25 20:18:46Z

3

assuming a dummy record looks like below

{
    "_id": 1,
    "Region": "North America",
    "TotalSales": 200000,
    "Countries": [
      {
        "name": "USA",
        "sales": 150000
      },
      {
        "name": "Canada",
        "sales": 50000
      },
      {
        "name": "Mexico",
        "sales": 10000
      },
      {
        "name": "Greenland",
        "sales": 5000
      },
      {
        "name": "Cuba",
        "sales": 2000
      },
      {
        "name": "Bahamas",
        "sales": 1000
      },
      {
        "name": "Jamaica",
        "sales": 500
      },
      {
        "name": "Haiti",
        "sales": 200
      },
      {
        "name": "Dominican Republic",
        "sales": 100
      }
    ]
  }

and that you want to sort by country sales field you can use the $sortArray operator (version 5.2 onwards) inside $slice in the $project stage

db.collection.aggregate([
  {
    $project: {
      Region: 1,
      TotalSales: 1,
      Countries: {
        $slice: [
          {
            $sortArray: {
              input: "$Countries",
              sortBy: {
                sales: 1
              },    
            }
          },
          5
        ]
      }
    }
  }
])

playground

if array doesn't have fields (something like ["UK","USA",..]) use

              input: "$Countries",
              sortBy: 1 //direction of sort

for versions 5.1 and below $sortArray doesn't work and you will have to go for a $unwind, $sort, $group, $push combo. see here

the query then will look like

db.collection.aggregate([
  {
    $unwind: "$Countries"
  },
  {
    $sort: {
      "Countries.sales": 1
    }
  },
  {
    $group: {
      _id: "$_id",
      Countries: {
        $push: "$Countries"
      },
      Region: {
        $first: "$Region"
      },
      TotalSales: {
        $first: "$TotalSales"
      }
    }
  },
  {
    $project: {
      Region: 1,
      TotalSales: 1,
      Countries: {
        $slice: [
          "$Countries",
          5
        ]
      }
    }
  }
])

playground

edited Mar 25, 2023 at 20:18

answered Mar 25, 2023 at 18:50

cmgchess

10.3k42 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sandeep.kgp Over a year ago

Hey, thanks for the response...it does work for v5.2+ but the version that I'm using is v5.0..is there any way to achieve the same functionality in v5 ?

cmgchess Over a year ago

must be possible according to docs The $sortArray syntax and semantics are the same as the behavior in a $push operation modified by $sort.

sandeep.kgp Over a year ago

$sortArray was introduced for v5.2 and above..on using it in v5 it throws InvalidPipelineOperator error

cmgchess Over a year ago

yeah need to figure out the equivalent using push and sort. let me see

sandeep.kgp Over a year ago

Does the job...thanks.

Collectives™ on Stack Overflow

MongoDB sort array before $slice

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related