Node.js : How to return Object in Query Aggregation?

Question

I need to calculate sum for specified field from my collection using group by

So that, I used aggregate function.

My wish is function should return Object only. When I use aggregate, it returns the Array of Objects.

But my query was returns Array of objects ([{..}, {..}, {..}]) not single object({..}) .

For example, So far I tried

Specified with Array [ ] structure

MyModel.aggregate(
  [
    {$match : ... },
    {$group : ... }
  ], function(err, result) {
      console.log(result);
}); 

Specified without Array [ ] structure

MyModel.aggregate(
    {$match : ... },
    {$group : ... }
  , function(err, result) {
      console.log(result);
});

The above both scenarios returns the same. like,

[
  {
    ...
    ...
  }
]

My expected result only an object

{
  ...
  ...
}

Solution 1

Officially, the aggregate command takes a "list" of pipeline arguments as it's main input. This is why the "Array" syntax is preferred. Though it is not strictly implemented this way by the mongoose method you appear to be using, this is likely to change.

The "Official" arguments are

1. An array representing the pipeline conditions

2. An options document specifying various options to alter the behavior.

So the mongoose form here is just looking for which item in the "list" is a function() and considers everything beforehand to be pipeline. But this will have to change in the future. Your question though seems to be more about "expecting" the different form to produce either a "document" or an "array" in response. But this will not happen.

You don't really and there is a good reason why. The aggregation framework either performs manipulation on collection content just as with $project or otherwise "groups" information such as with $group.

The only ways in which you generally expect only "one" result to return is when you either specifically $match to obtain only one result or otherwise $limit the response to one or even if your $group is on a singular or null value.

It is generally expected to be a "collection" of results or here represented as an array. Modern MongoDB servers and driver implementations can in fact return a "cursor", so the results can be larger than the 16MB BSON limit with newer versions. But still basically a list.

So it's always an array at present with something like "mongoose", which it looks like you are using. Just return the "first element" when it is all you want. Possibly even limit to 1 as well:

MyModel.aggregate(
  [
    { "$match": ... },
    { "$group": ... },
    { "$limit": 1 }
  ], function(err, result) {
      console.log(result[0]);
});

Which is essentially all that methods like .findOne() are doing under the covers anyway.

Solution 2

In my case it return the data like follow

[ 
    {
            _id: 5e5e43b356602513c00a41aa,
            name: 'example',
            email: '[email protected]',
            gender: 'male',
            hobbies: 'sing,guitarplay',
            countryid: 5e4bc85ec0b157304fbd7c50,
            cityid: 5e55faa52d138807addca5b7,
            date: 2020-03-03T11:46:59.119Z,
            cityinfo: [ [Object] ],
            statesinfo: [ [Object] ],
            countryinfo: [ [Object] ] 
    }
]

and my code is as follow

router.get('/', ensureAuthenticated, (req, res) => {

Student.aggregate([
        {
           $lookup: {
               from: "cities",
               localField: "cityid",
               foreignField: "_id",
               as: "cityinfo",
           }
       },
       {
           $lookup:{
               from: 'states',
               localField: 'stateid',
               foreignField: '_id',
               as: 'statesinfo'
           }
       }, 
       {
           $lookup: {
               from: "countries",
               localField: "countryid",
               foreignField: "_id",
               as: "countryinfo",
           }
       },
       
       {
           "$project": {
               "stateid": 0,
               "__v": 0,
               "countryinfo._id": 0,
               "countryinfo.__v": 0,
               "countryinfo.date": 0,
               "statesinfo._id": 0,
               "statesinfo.date": 0,
               "statesinfo.__v": 0,
               "statesinfo.countryid": 0,
               "cityinfo._id": 0,
               "cityinfo.__v": 0,
               "cityinfo.date": 0,
               "cityinfo.stateid": 0,

           }
   }
   ]).then(studentList => {
       console.log(studentList);            
    });

});

so after that i just change my code with following code like follow

router.get('/', ensureAuthenticated, (req, res) => {

Student.aggregate([
        {
           $lookup: {
               from: "cities",
               localField: "cityid",
               foreignField: "_id",
               as: "cityinfo",
           }
       },
       {
           $lookup:{
               from: 'states',
               localField: 'stateid',
               foreignField: '_id',
               as: 'statesinfo'
           }
       }, 
       {
           $lookup: {
               from: "countries",
               localField: "countryid",
               foreignField: "_id",
               as: "countryinfo",
           }
       },
       {
        $unwind: "$cityinfo",
       },
       {
        $unwind: "$statesinfo",
       },
       {
        $unwind: "$countryinfo",
       },
       {
           "$project": {
               "stateid": 0,
               "__v": 0,
               "countryinfo._id": 0,
               "countryinfo.__v": 0,
               "countryinfo.date": 0,
               "statesinfo._id": 0,
               "statesinfo.date": 0,
               "statesinfo.__v": 0,
               "statesinfo.countryid": 0,
               "cityinfo._id": 0,
               "cityinfo.__v": 0,
               "cityinfo.date": 0,
               "cityinfo.stateid": 0,

           }
   }
   ]).then(studentList => {
       console.log(studentList);
    });

});

now it produce following output

[
  { _id: 5e5e494e3823c216a2c7ab01,
    name: 'example',
    email: '[email protected]',
    gender: 'male',
    hobbies: 'sing,guitarplay,cricket',
    countryid: 5e4bc85ec0b157304fbd7c50,
    cityid: 5e55faa52d138807addca5b7,
    date: 2020-03-03T12:10:54.654Z,
    cityinfo: { cityname: 'Nathdwara' },
    statesinfo: { statename: 'Rajasthan' },
    countryinfo: { countryname: 'India' } 
  } 
]

hope it works..

Solution 3

I needed to accomplish something similar to the above (i.e. use aggregation pipeline -- in my case to do a "join" using $lookup -- and similarly returning a single (non-array) object only). However, I also needed to return a Promise for consistency. I added a then after the .exec as follows to extract the object:

return myModel
.aggregate([
  { $match: {
    _id: ObjectId(args._id)} 
  },
  {
    $lookup: {
      from: "items",
      localField: "_id",
      foreignField: "itemId",
      as: "items"
    }
  }
])
.exec(function(err, pipelines) {
  if (err) {
    console.log('Error: ', err);
    return Promise.reject(new InternalError());
  }
})
.then(items => items[0]);

Solution 4

var url = "mongodb://127.0.0.1:27017/";

MongoClient.connect(url, function(err, db) {
  if (err) throw err;
  var dbo = db.db("progettotestesi12");
  dbo.collection('lavoros').aggregate([{
    $lookup: {
      from: 'tempiobbiettivos',
      localField: 'codicearticolo',
      foreignField: 'cod_prodotto_su_cdl',
      as: 'collegamentotempiobbiettivo'
    }
  }]).toArray(function(err, result) {
    if (err) throw err;
    console.log(result[0].collegamentotempiobbiettivo[0].cdl);

    res.render('pannello/testdiroman', {
      showrisultati: result,
    });
  });
});

i had the same problem as you can see from the picture, my object was {[]} instead of {}. i managed to return the variable using my aggragation variable collegamentotempiobbiettivo[0]."name of the variable i wanted to recall". example collegamentotempiobbiettivo[0].cld will return B119

View more solutions

Comments

[Ranjith]

Okies. But When I define or undefined of Array [..] braces in query, I got the same results in always. Then What is the use between both of them?

[Neil Lunn]

@Ranjith Not sure what you mean here. Are you expecting one result or many? The many case is why the result is an array without using limit to keep that array at only 1 element. Why should the value change on different attempts? There is not even a query in your question to explain what you are doing.

[Ranjith]

I'm not worrying about any results here. Just I ask about this query structure. Please read my question under the Specified with Array [ ] structure and Specified without Array [ ] structure. These both query will returns the same results which is Array of Objects like [{..}]. Is there any difference?

[Neil Lunn]

@Ranjith Yes but you seem to be concluding this means a different result output. It does not. More information added to the answer. Re-read it.

[Roman Kis]

updated with the code. But the picture is a console.log screenshot, so i wont update that sorry

[Alvaro Carvalho]

Is it possible to perform $unwind in order to return an object in the end, instead of an array of size 1? something like $unwind for the whole collection in which agreggate was called upon.