[SOLVED] Aggregate showing different results. Why?!

fudgemaker · October 28, 2017, 12:31am

If I run this aggregation in Mongo Shell

db.objects.aggregate({ $unwind: '$bids' }, { $project: { bids: "$bids" } }, { $sort : { "bids.placed" : -1 } });

I get the result:

{ "_id" : "RN9Zejme54AhN6EZg", "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T13:15:00Z") } }
{ "_id" : "RN9Zejme54AhN6EZg", "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T13:10:00Z") } }
{ "_id" : "WDnmJ3x6EsX4dboWy", "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T13:05:00Z") } }
 { "_id" : "WDnmJ3x6EsX4dboWy", "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T13:00:00Z") } }

Everything is perfect and the aggregations returns the expected result.

But when I run this in Meteor, I only get half the results back. It seems that there is some kind of filtering on “_id” that only returns one post per _id.

{ "_id" : "RN9Zejme54AhN6EZg", "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T13:15:00Z") } }
 { "_id" : "WDnmJ3x6EsX4dboWy", "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T13:05:00Z") } }

I use the jcbernack:reactive-aggregate@0.7.0 package and use this code

On server

Meteor.publish("serverBids", function() {
  ReactiveAggregate(this, Objects, [
    { $unwind: '$bids' }, { $project: { bids: "$bids" } }, { $sort : { "bids.placed" : -1 } }
  ], { clientCollection: "clientBids" });
});

On client

Meteor.subscribe("serverBids");
clientBids = new Mongo.Collection("clientBids");

Template.adminDashboard.helpers({
  serverBids: function(limit) {
    return clientBids.find();
  }
});

What am I missing here?!

issa · October 28, 2017, 1:23pm

the ids of documents are duplicated. you have to change the ids.

fudgemaker · October 29, 2017, 9:55pm

Thanks issa. I figured that it has to do with that somehow. I guess I have to filter away the ids somehow. I don’t need to get them in the result.

azrael188 · October 31, 2017, 11:13pm

I think you might want to look into why you have duplicate _ids in the first place when _id is meant to be unique? Thats going to affect more than just your aggregate function but the way you retrieve a single record for viewing or edit later.

robfallows · November 1, 2017, 10:10am

That’s normal with the $unwind operator. However, they can be excluded from the final result.

fudgemaker · November 1, 2017, 12:43pm

Can you or anyone point me in the direction on how do I exclude them from the final result?

robfallows · November 1, 2017, 12:45pm

https://docs.mongodb.com/manual/reference/operator/aggregation/project/

fudgemaker · November 1, 2017, 1:33pm

This is my problem. I get perfect results in the Mongo Shell. But when I try to execute the same thing in Meteor, I get a total different result.

If I execute

db.objects.aggregate([ { $unwind: '$bids' }, { $project: { bids: "$bids", _id: 0 } }, { $sort : { "bids.placed" : -1 } } ] );

I get the full result… All rows.

{ "bids" : { "user" : "ySjSRA7T9WcmMyfpJ", "amount" : 300, "placed" : ISODate("2017-11-09T12:01:00Z") } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 500, "placed" : ISODate("2017-10-27T15:20:50.366Z") } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 1200, "placed" : ISODate("2017-10-24T15:21:03.338Z") } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 750, "placed" : ISODate("2017-10-24T15:20:39.305Z") } }
{ "bids" : { "user" : "ySjSRA7T9WcmMyfpJ", "amount" : 300, "placed" : ISODate("2017-10-10T12:01:00Z") } }
{ "bids" : { "user" : "ySjSRA7T9WcmMyfpJ", "amount" : 300, "placed" : ISODate("2017-10-09T12:01:00Z") } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 200, "placed" : ISODate("2017-10-09T12:00:00Z") } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 200, "placed" : ISODate("2017-10-09T12:00:00Z") } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 200, "placed" : ISODate("2017-10-09T12:00:00Z") } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 200, "placed" : ISODate("2017-10-09T12:00:00Z") } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 200, "placed" : ISODate("2017-10-09T12:00:00Z") } }
{ "bids" : { "user" : "ySjSRA7T9WcmMyfpJ", "amount" : 500, "placed" : ISODate("2017-10-09T11:01:00Z") } }
{ "bids" : { "user" : "ySjSRA7T9WcmMyfpJ", "amount" : 300, "placed" : ISODate("2017-10-09T11:01:00Z") } }
{ "bids" : { "user" : "ySjSRA7T9WcmMyfpJ", "amount" : 300, "placed" : "2017-10-09T12:01:00.000Z" } }
{ "bids" : { "user" : "ySjSRA7T9WcmMyfpJ", "amount" : 300, "placed" : "2017-10-09T12:01:00.000Z" } }
{ "bids" : { "user" : "ySjSRA7T9WcmMyfpJ", "amount" : 300, "placed" : "2017-10-09T12:01:00.000Z" } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 200, "placed" : "2017-10-09T12:00:00.000Z" } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 200, "placed" : "2017-10-09T12:00:00.000Z" } }
{ "bids" : { "user" : "hYC5N63Ew7bZE7T3F", "amount" : 200, "placed" : "2017-10-09T12:00:00.000Z" } }

But when I do it in Meteor by jcbernack:reactive-aggregate, and have _id: 0 in $project I only get one row!

{ "bids" : { "user" : "ySjSRA7T9WcmMyfpJ", "amount" : 300, "placed" : ISODate("2017-10-09T11:01:00Z") } }

Without the exclusion if _id , I get half the rows. I don’t get it.

robfallows · November 1, 2017, 2:52pm

Ah - you’re using an outdated Atmosphere package for aggregation.

Do you need reactivity? If not, you can use the underlying aggregate method exposed on the rawCollection().

fudgemaker · November 1, 2017, 3:01pm

I would like to show the latest X amount of bids made in an auction site to the Admin in the Admin Dashboard. Hence the need for reactivity. I would be neat for the Admin to be able to follow the action on the bids made in an auction as it happens without reloading the page. I guess I will have to abandon this great endeavor then?

robfallows · November 1, 2017, 3:06pm

Not necessarily. You could clone the package and update it. If you also submit a PR, we can all benefit!

EDIT: Just looked at the GH repo for the package. It’s pretty simple, but uses the meteorhacks:aggregate package, which is most likely the source of your problem. Removing this and rewriting the package code to use more modern async/await syntax with rawCollection() should be straightforward and more future-proof.

fudgemaker · November 1, 2017, 3:38pm

Allright. I will look into it. Thank you.

robfallows · November 2, 2017, 2:26pm

I’ve just published tunguska:reactive-aggregate, which basically does what I suggested above.

I’d appreciate it, if you could test and report back (it works for me) .

fudgemaker · November 2, 2017, 4:40pm

Awesome! You beat me to it! I will test it tonight.

fudgemaker · November 2, 2017, 10:04pm

I have a collection with roughly the following structure:

{ 
	"_id"
	"status"
	"bids" [ 
	{ 
		"user", 
		"amount", 
		"placed" 
	},
	{ 
		"user", 
		"amount", 
		"placed" 
	}
	], 
	"closingAt", 
	"createdAt" 
}

I want to retrieve a list of latest bids from this collection. The bids for each auction object is stored under the array ‘bids’.

By using aggregate, I am able to get my desired result in Mongo Shell

db.objects.aggregate({ $unwind: '$bids' }, { $project: { bids: "$bids", _id: 0 } }, { $sort : { "bids.placed" : -1 } });

gives

{ "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T13:15:00Z") } }
{ "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T13:10:00Z") } }
{ "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T13:05:00Z") } }
{ "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T13:00:00Z") } }
{ "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T12:55:00Z") } }
{ "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:50:00Z") } }
{ "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T12:45:00Z") } }
{ "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:40:00Z") } }
{ "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:35:00Z") } }
{ "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T12:30:00Z") } }
{ "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T12:25:00Z") } }
{ "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:20:00Z") } }
{ "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T12:15:00Z") } }
{ "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:10:00Z") } }
{ "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T12:05:00Z") } }
{ "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:00:00Z") } }

Using the jcbernack:reactive-aggregate and your package gives me

a) with _ids - { $project: { bids: “$bids” } }

{ "_id" : "RN9Zejme54AhN6EZg", "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T13:10:00Z") } }
{ "_id" : "WDnmJ3x6EsX4dboWy", "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T13:00:00Z") } }
{ "_id" : "RrpwdyN5YzvHHKqBn", "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:50:00Z") } }
{ "_id" : "pWi9yKEAkrChQqv84", "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:40:00Z") } }
{ "_id" : "YAcsjw4Hm32rPvz77", "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T12:30:00Z") } }
{ "_id" : "kvPHx2TCwoo3M7M3o", "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:20:00Z") } }
{ "_id" : "D53zkHbv9YNY8YXfi", "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:10:00Z") } }
{ "_id" : "Jh6chaQCh9woLiLDk", "bids" : { "user" : "SH5DqfnH5gSYTTHHa", "amount" : 200, "placed" : ISODate("2017-10-09T12:00:00Z") } }

b) excluding the _ids - { $project: { bids: “$bids”, _id: 0 }

Gives an error

Uncaught Error: Expected to find a document to change
at Object.update (http://localhost:3000/packages/mongo.js?hash=191d09671a8d33340d4acaaa8c6015f73cc58072:245:27)
at Object.store.(anonymous function) [as update] (http://localhost:3000/packages/ddp-client.js?hash=8b46f76bbf82e182fe81190d0eea965a685cbd77:3732:60)
at http://localhost:3000/packages/ddp-client.js?hash=8b46f76bbf82e182fe81190d0eea965a685cbd77:4539:19
at Array.forEach (<anonymous>)
at Function._.each._.forEach (http://localhost:3000/packages/underscore.js?hash=cde485f60699ff9aced3305f70189e39c665183c:149:11)
at http://localhost:3000/packages/ddp-client.js?hash=8b46f76bbf82e182fe81190d0eea965a685cbd77:4538:13
at Function._.each._.forEach (http://localhost:3000/packages/underscore.js?hash=cde485f60699ff9aced3305f70189e39c665183c:157:22)
at Connection._performWrites (http://localhost:3000/packages/ddp-client.js?hash=8b46f76bbf82e182fe81190d0eea965a685cbd77:4534:9)
at Connection._flushBufferedWrites (http://localhost:3000/packages/ddp-client.js?hash=8b46f76bbf82e182fe81190d0eea965a685cbd77:4521:10)
at Connection._livedata_data (http://localhost:3000/packages/ddp-client.js?hash=8b46f76bbf82e182fe81190d0eea965a685cbd77:4487:12)

This line gives me the error:

} else if (msg.msg === 'changed') {
  if (!doc)
	  throw new Error("Expected to find a document to change");

And returns only one array in the result

{ "bids" : { "user" : "j3d8a58AQwngD4gEL", "amount" : 300, "placed" : ISODate("2017-10-09T13:15:00Z") } }

robfallows · November 3, 2017, 11:49am

Ah. Your problem is that using pub/sub means that the result of the aggregation must be able to be “replayed” into minimongo. Minimongo uses the document’s _id field as the unique identifier for each document - that’s quite reasonable, because it’s the same bahaviour seen in any collection. However, duplicate _ids from an aggregation will be collapsed into one “winner”. That’s the behaviour you reported initially.

Removing the _id from the resulting cursor will not work - the minimongo collection needs that _id to identify changes to each document.

That means you must rewrite the _id to something unique if you want to keep each unwound document in the final client-side collection. If you’re using MongoDB 3.2 or above, you could do something like:

ReactiveAggregate(this, MyCollection, [
  {
    $unwind: {
      path: '$bids',
      includeArrayIndex: 'suffix'
    }
  }, {
    $project: {
      _id: {
        $concat: [
          '$_id',
          '_',
          {
            $substr: ['$suffix', 0, -1]
          }
        ]
      }
    }
  }, {
    $sort: {
      'bids.placed': -1
    }
  }
]);

fudgemaker · November 5, 2017, 9:41pm

Very helpful, Rob! Now I get all the data.
But it is not reactive for me. If I insert a new bid, I need to manually refresh the page. Any ideas why that might be? As you see below, I only added the bids: ‘$bids’ to $project. Does this create a problem for reactiveness? I my mind, this should work since it works in the mongo Shell.

server/main.js

 Meteor.publish("serverBids", function() {
   ReactiveAggregate(this, Objects, [
     {
       $unwind: {
         path: '$bids',
         includeArrayIndex: 'suffix'
       }
     }, {
       $project: {
         _id: {
           $concat: [
             '$_id',
             '_',
             {
               $substr: ['$suffix', 0, -1]
             }
           ]
         },
         **bids: '$bids'**
       }
     }, {
       $sort: {
         'bids.placed': -1
       }
     }
 ],
   { 
     clientCollection: "clientBids" 
   }
 );
 });

client/dashboard.js

 const clientBids = new Mongo.Collection("clientBids");
 
 Template.adminDashboard.helpers({
   latestBids: function(limit) {
     //console.log("I'm working");
     console.log(clientBids.find());
     return clientBids.find();
   }
 });
 
 Template.adminDashboard.onCreated(function() {
   // subscribe to the aggregation
   this.subscribe('serverBids');
 });

client/dashboard.html

 {{#each latestBids}}
    <tr>
       <td>{{bids.user}}</td>
       <td>{{formatDate bids.placed 'YYYY-MM-DD HH:mm:ss'}}</td>
       <td>{{bids.amount}}</td>
    </tr>
 {{/each}}

robfallows · November 6, 2017, 4:32pm

Hmm, my test code worked reactively. The documentation for $project suggests that for inclusion of an existing field, you would use 1 or true (bids: 1, rather than the expression bids: '$bids'). However, I’d be surprised if that was the issue.

BTW, your code samples would be easier to read if you wrapped them in lines with triple-backticks:

```
like
this
```

fudgemaker · November 7, 2017, 12:25am

Updated the thread with backtips for readability. I tried bids: 1 and bids: true. Unfortunately none of them works for me. Still not reactive I’m afraid. I will test a couple of other different setups, just to give you feedback on how your package works. But as far as my scenario goes, it is simply not working as expected.

robfallows · November 7, 2017, 9:46am

PM me with some actual data I can insert into MongoDB, along with the mutation you’re expecting to see reactively and I’ll take a look.