Add sequence number to items from mongodb query

user_9184865 0
Viewed 1129

I have MongoDB collection items with following documents:

{ "name": "First", "value": 8 },
{ "name": "Second", "value": 2 },
{ "name": "Third", "value": 5 }

I would like to sort them by name or value and then add sequence number (index property) to each of them (first item will have "index": 1, second "index": 2, etc.). So for this query:

db.items.aggregate([{ $sort: { "value": 1 } }])

result should be:

{ "name": "Second", "value": 2, "index": 1 },
{ "name": "Third", "value": 5, "index": 2 },
{ "name": "First", "value": 8, "index": 3 }

This should also works with $skip. With { "$skip": 1 }, result should be items with indexes 2, 3, not 1, 2. Is there any way to do this using pure MongoDB, without any application logic?

mongodb sorting
2 Answers

Might be this is one option you can do with includeArrayIndex in $unwind,

  • $sort by value that you already did
  • $group by name and value and push in items
  • $unwind deconstruct items and set field index in includeArrayIndex
  • $project required fields and increment index with because it start with 0 zero
db.collection.aggregate([
  { $sort: { value: 1 } },
  {
    $group: {
      _id: 1,
      items: {
        $push: {
          name: "$name",
          value: "$value"
        }
      }
    }
  },
  {
    $unwind: {
      path: "$items",
      includeArrayIndex: "index"
    }
  },
  {
    $project: {
      _id: 0,
      name: "$items.name",
      value: "$items.value",
      index: { $add ["$index", 1] }
    }
  }
])

Playground: https://mongoplayground.net/p/572ReIpRcl6

user_89871280

Note: Inspired by @turvishal's answer to meet the below things

  1. Index starting from 1
  2. Skip the way OP wants

Mongo-play

db.collection.aggregate([
  {
    $sort: {
      value: 1
    }
  },
  {
    $group: {
      _id: 1,
      items: {
        $push: {
          name: "$name",
          value: "$value"
        }
      }
    }
  },
  {
    $unwind: {
      path: "$items",
      includeArrayIndex: "index"
    }
  },
  {
    $project: {
      _id: 0,
      name: "$items.name",
      value: "$items.value",
      index: { //Incrementing the index by 1
        $add: [
          "$index",
          1
        ]
      }
    }
  },
  { //Addition of skip 
    "$skip": 1
  }
])
user_26941840
Related