Sort operations on compound multikey index

Working with Data
#1

I have a inventory collection with documents as shown below:

{
  _id: 1,
  item: "abc",
  stock: [
    { size: "S", color: "red", quantity: 25 },
    { size: "M", color: "blue", quantity: 50 }
  ]
}
{
  _id: 2,
  item: "def",
  stock: [
    { size: "S", color: "blue", quantity: 20 },
    { size: "M", color: "black", quantity: 10 },
    { size: "L", color: "red", quantity: 2 }
  ]
}
{
  _id: 3,
  item: "ijk",
  stock: [
    { size: "M", color: "blue", quantity: 15 },
    { size: "L", color: "blue", quantity: 100 }
  ]
}
...

I have created compound multikey index on:
db.inventory.createIndex( { "stock.size": 1, "stock.quantity": 1 } )

I want to query for docs where stock.size: "M" and all docs are sorted by it’s quantity in increasing order.
Expected result: [doc_2, doc_3, doc_1]

I am using query: db.inventory.find( { "stock.size": "M" } ).sort( { "stock.quantity": 1 } ), but it’s not returning results in sorted order as expected above.

Can someone please help here, how I can achieve same.
Thanks!!

#3

Hi @Upendra_Kumar
Welcome to the community forum!!

As per the query mentioned in the above topic, find().sort() would work on per-document basis and hence this would not mutate inside the document.

However, the desired output can be achieved using aggregation or by using aggregation operator $sortArray

However, here are two queries which would give the desired output:

  1. Making use of Index:
  db.inventory.aggregate(
   [
      {
      $match: 
            { "stock.size": "M" 
         }
      },
      { 
      $project: 
         { item: 
            { $filter: 
               { input: "$stock", 
               as: "item", 
               cond: { $eq: [ "$$item.size", "M" ] }
               } 
            } 
         } 
      } , 
      { 
      $sort: {"item.quantity": 1} 
      }
   ]
 )

Output:

[
  { _id: 2, item: [ { size: 'M', color: 'black', quantity: 10 } ] },
  { _id: 3, item: [ { size: 'M', color: 'blue', quantity: 15 } ] },
  { _id: 1, item: [ { size: 'M', color: 'white', quantity: 67 } ] }
]
  1. Not using Index
db.inventory.aggregate( {$unwind: "$stock" }, {$match: {"stock.size": "M"}}, { $sort: {"stock.quantity": 1}})
[
  {
    _id: 2,
    item: 'def',
    stock: { size: 'M', color: 'black', quantity: 10 }
  },
  {
    _id: 3,
    item: 'ijk',
    stock: { size: 'M', color: 'blue', quantity: 15 }
  },
  {
    _id: 1,
    item: 'abc',
    stock: { size: 'M', color: 'white', quantity: 67 }
  }
]

Adding a $group to group the documents based on stock field:

db.inventory.aggregate( {$unwind: "$stock" }, {$match: {"stock.size": "M"}}, { $sort: {"stock.quantity": 1}}, {$group: {_id: "$stock" } } )
[
  { _id: { size: 'M', color: 'black', quantity: 10 } },
  { _id: { size: 'M', color: 'blue', quantity: 15 } },
  { _id: { size: 'M', color: 'white', quantity: 67 } }
]

Please Note: The second aggregation query, the index would not be usable for the other stages other than $match.

Let us know if you have any further questions.

Best Regards
Aasawari