I am trying to export my data to an excel sheet from my web app. Im writing in node js next js and mongodb is the database. I am taking data from my database and running it into my app then making a table. the table seems to be organized by date time. I want it to be organized by first or last name, and then when it gets exported to excel it shows the _id field. It doesnt show the _id field in my app but i think because i dont have a column in the app, but why does it show in my excel sheet. I am a new developer and i just took on this app from previous developers and there is none of the old developers left.

I have tried to add $project and such but nothing i do when i change the file seems to do anything. the only file i could find that has anything that looks like it is dealing with the data shows as this:

export default async function handler(req,res){
    //dbConnect(req);
    let db = await dbConnect(req);

    const Persons_Type_Information = db.models.Persons_Type_Information || db.model("Persons_Type_Information",personTypesInformationSchema)
    
    const {method} = req;
    const {rolecd} = req.query;

    switch (method){
        case "GET":
            let resp = [];
            try{
                let responses = await Persons_Type_Information.aggregate([
                    { $match: {
                        $expr: {
                          $and: [
                            {$eq: [{
                              $dateToString: {
                                date: {
                                  $subtract: [
                                    new Date(),
                                    0
                                  ]
                                },
                                format: '%Y-%m-%d'
                              }
                            },
                              {$dateToString: {
                                date: '$pti_signed_in_date',
                                format: '%Y-%m-%d'
                              }
                            }]},
                            {$or: [
                              {$eq: [
                                "$pt_type",
                                "Employee"
                              ]},
                              {$eq: [
                                "$pt_type",
                                "Contractor"
                              ]}
                            ]}
                          ]
                        },
                      } },
                    { $group: 
                        {
                            _id: {$cond: {
                              'if': {
                                $eq: [
                                  '$pti_visiting_location',
                                  ''
                                ]
                              },
                              then: 'Employee',
                              'else': '$pti_visiting_location'
                            }},
                            count: {
                              $sum: {
                                $cond: {
                                  'if': {
                                    $ne: [
                                      '$pti_signed_in_date',
                                      ''
                                    ]
                                  },
                                  then: 1,
                                  'else': 0
                                }
                              }
                            }
                            
                          
                            
                          }, 
                    }
                    ]);
                                     
                return res.json({
                //    data: JSON.parse(JSON.stringify(resp)),
                    data : JSON.parse(JSON.stringify(responses)),
                    success: true
                    
                }),
                console.log(res);
                
            }catch(error){
                return res.send({
                    data : new Error(error).message,
                    success: false
                })
            }

        default:

Am i looking in the wrong spot? i feel like this is the right spot

CodePudding user response：

_id is not appearing due to the $group stage.

CodePudding user response：

##You can use $sort in the aggregate pipeline to sort the results by a certain field, and $project to specify which fields should be returned in the final result. ##

let responses = await Persons_Type_Information.aggregate([
    { $match: {
        $expr: {
            $and: [
                {$eq: [{
                  $dateToString: {
                    date: {
                      $subtract: [
                        new Date(),
                        0
                      ]
                    },
                    format: '%Y-%m-%d'
                  }
                },
                  {$dateToString: {
                    date: '$pti_signed_in_date',
                    format: '%Y-%m-%d'
                  }
                }]},
                {$or: [
                  {$eq: [
                    "$pt_type",
                    "Employee"
                  ]},
                  {$eq: [
                    "$pt_type",
                    "Contractor"
                  ]}
                ]}
            ]
        },
      } },
    { $group: 
        {
            _id: {$cond: {
              'if': {
                $eq: [
                  '$pti_visiting_location',
                  ''
                ]
              },
              then: 'Employee',
              'else': '$pti_visiting_location'
            }},
            count: {
              $sum: {
                $cond: {
                  'if': {
                    $ne: [
                      '$pti_signed_in_date',
                      ''
                    ]
                  },
                  then: 1,
                  'else': 0
                }
              }
            }
        }, 
    },
    { $sort: { first_name: 1 } },
    { $project: { _id: 1, first_name: 1 } }
]);

above is an example that sorts by first name and includes only the _id and first name fields in the final result.