Add number field in $project mongodb-CodePudding

I have an issue that need to insert index number when get data. First i have this data for example:

[
 {
  _id : 616efd7e56c9530018e318ac
  student : {
     name: "Alpha"
     email: null
     nisn: "0408210001"
     gender : "female" 
  }
 },
 {
  _id : 616efd7e56c9530018e318af
  student : {
     name: "Beta"
     email: null
     nisn: "0408210001"
     gender : "male" 
  }
 }
]

and then i need the output like this one:

[
 {
  no:1,
  id:616efd7e56c9530018e318ac,
  name: "Alpha",
  nisn: "0408210001"
 },
 {
  no:2,
  id:616efd7e56c9530018e318ac,
  name: "Beta",
  nisn: "0408210002"
 }
]

i have tried this code but almost get what i expected.

    {
    '$project': {
      '_id': 0, 
      'id': '$_id', 
      'name': '$student.name', 
      'nisn': '$student.nisn'
    }
  }

but still confuse how to add the number of index. Is it available to do it in $project or i have to do it other way? Thank you for the effort to answer.

CodePudding user response：

You can use $unwind which can return an index, like this:

db.collection.aggregate([
  {
    $group: {
      _id: 0,
      data: {
        $push: {
          _id: "$_id",
          student: "$student"
        }
      }
    }
  },
  {
    $unwind: {path: "$data", includeArrayIndex: "no"}
  },
  {
    "$project": {
      "_id": 0,
      "id": "$data._id",
      "name": "$data.student.name",
      "nisn": "$data.student.nisn",
      "no": {"$add": ["$no",  1] }
    }
  }
])

You can see it works here .

I strongly suggest to use a $match step before these steps, otherwise you will group your entire collection into one document.

CodePudding user response：

You need to run a pipeline with a $setWindowFields stage that allows you to add a new field which returns the position of a document (known as the document number) within a partition. The position number creation is made possible by the $documentNumber operator only available in the $setWindowFields stage.

The partition could be an extra field (which is constant) that can act as the window partition.

The final stage in the pipeline is the $replaceWith step which will promote the student embedded document to the top-level as well as replacing all input documents with the specified document.

Running the following aggregation will yield the desired results:

db.collection.aggregate([
    { $addFields: { _partition: 'students' }},
    { $setWindowFields: {
        partitionBy: '$_partition',
        sortBy: { _id: -1 },
        output: { no: { $documentNumber: {} } }
    } },
    { $replaceWith: { 
        $mergeObjects: [ 
            { id: '$_id', no: '$no' }, 
            '$student' 
        ] 
    } }
])